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?

Did the migration complete sucessfully?

Also you say you try to enlarge the space and that is also full :confused: 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

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:confused: , why???? I can't answer,,,
I created a new other database, but also I found the TEMP tablespace is full :confused: 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.

Quote:

---

[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.

Quote:

---

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?

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.