-
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.
-
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!
-
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.
-
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?
-
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.
-
[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
-
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.
-
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
-
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.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|