DBAsupport.com Forums - Powered by vBulletin
Results 1 to 9 of 9

Thread: ORA-03217: invalid option for alter of TEMPORARY TABLESPACE

  1. #1
    Join Date
    Aug 2001
    Posts
    27

    ORA-03217: invalid option for alter of TEMPORARY TABLESPACE

    Hi,,,
    Oracle DBMS: 9i R2
    O.S.: HP-UX 11i

    I was upgrade Oracle from 8.1.7 to 9.2.0.1,,,
    and finished from migrate data from old to new database,,,
    but I found the performance is slow,,,
    when I searched about any problem I found the the TEMP tablespace is full, I try to enlarge the space but it was still full, I try to make it offline but I can't, the error message was appear:
    ORA-03217: invalid option for alter of TEMPORARY TABLESPACE

    I try to create another new TEMP tablespace but it also full,,,

    What I do to solve this problem?
    Last edited by abcd; 03-05-2003 at 10:44 AM.

  2. #2
    Join Date
    Jan 2002
    Location
    Up s**t creek
    Posts
    1,525
    Did the migration complete sucessfully?

    Also you say you try to enlarge the space and that is also full Do you mean that as ssson as the datafile is added it is filled?

    Do you have a large sort job running which would cause the temporary tablespace to fill? What errors are occuring in the alert log?

    Regards
    Last edited by jovery; 03-05-2003 at 10:53 AM.
    Jim
    Oracle Certified Professional
    "Build your reputation by helping other people build theirs."

    "Sarcasm may be the lowest form of wit but its still funny"

    Click HERE to vist my website!

  3. #3
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    all the space in the temp tablespace may be allocated, but that doesn't mean it's being used. oracle no longer de-allocates from temp -- it re-uses instead.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  4. #4
    Join Date
    Aug 2001
    Posts
    27
    thank you jovery, slimdave for resoponse,,

    jovery,,
    Q: Did the migration complete sucessfully?
    A: I migrate the schema by schema, so I think this succses,

    Q: you say you try to enlarge the space and that is also full Do you mean that as ssson as the datafile is added it is filled?
    A: I trid to add new datafile to the TEMP tablespace but I found this datafile is full , why???? I can't answer,,,
    I created a new other database, but also I found the TEMP tablespace is full I tried to get it offline, the ORA-03217 error appeared,,,

    Q: Do you have a large sort job running which would cause the temporary tablespace to fill?
    A: however, the performance of old databse is good,,,

    slimdave,,,
    Why when I tried to get the TEMP tablespace offline, the ORA-03217 error did appear?

  5. #5
    Join Date
    Jun 2001
    Location
    Dublin, Ireland
    Posts
    66
    Hi
    what command did you use to offline the tablespace?
    ora 03217 changes with 9i

    error for 8.1.7

    oerr ora 03217
    03217, 00000, "invalid option for alter of TEMPORARY TABLESPACE"
    // *Cause: invalid option for alter of temporary tablespace was specified
    // *Action: Specify one of the valid options: ADD TEMPFILE


    error for 9i

    ORA-03217 invalid option for alter of TEMPORARY TABLESPACE

    Cause: Invalid option for alter of temporary tablespace was specified.

    Action: Specify one of the valid options: ADD TEMPFILE, TEMPFILE ONLINE, TEMPFILE OFFLINE.

  6. #6
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    [slimdave,,,
    Why when I tried to get the TEMP tablespace offline, the ORA-03217 error did appear?
    don't know don't care. I've seen this before -- people try to impose their own notions of how temporary tablespaces work in 8i/9i based on experiences in previous versions. the tablespace appears to be full, so dba's keep adding more datafiles. the ts is not full at all, just the way that the space is allocated has changed.

    Read the documentation
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  7. #7
    Join Date
    Dec 2001
    Location
    Baltimore, MD
    Posts
    374
    SlimDave
    It is not that people try to impose their own opinions on how temp tablespace works or should work. I am still running on 8.1.7.0 and dba before me had nothing but DMT in the database. I recreated a new database and made every tablespace LMT except the system and come to find out the temp tablespace is always full and went up to 10 gig. If you dont add datafile users cannot perform any sort and you get bombarded with calls and you see your temp tablespace growing and growing because you keep adding datafiles.

    So after investigation and after reading the documentation, I decided to simply use DMT for the temp tablespace and since then no problem. I can manually deallocate when need be and when there is no sorting going on since I cannot afford to shutdown the database every time I have full temp tablespace issue.
    Arsene Lupain
    The lie takes the elevator, the truth takes the staircase but ends up catching up with the lie.

  8. #8
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    Originally posted by Ablakios
    I can manually deallocate when need be and when there is no sorting going on since I cannot afford to shutdown the database every time I have full temp tablespace issue.
    Just trying to understand here...
    You deallocate TEMP space at one point just to have the users use it again sometime later?
    Jeff Hunter

  9. #9
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    I'm betting, though, that you weren't bombarded with calls that users were getting out-of-space errors. It wasn't "full", just the space was allocated and not deallocated, just as designed.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width