If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

* ALTER TABLESPACE TEMP ADD TEMPFILE 'new_temp_file'
* ALTER DATABASE TEMPFILE 'old_temp_file' OFFLINE;
* ALTER DATABASE TEMPFILE 'old_temp_file' DROP;this is often not possible, as users may have space allocated in the tempfile. Be patient, kill the sessions or restart the database
* go to the OS and remove the old_temp_file

AlesThe whole difference between a little boy and an adult man is the price of toys

* ALTER TABLESPACE TEMP ADD TEMPFILE 'new_temp_file'
* ALTER DATABASE TEMPFILE 'old_temp_file' OFFLINE;
* ALTER DATABASE TEMPFILE 'old_temp_file' DROP;this is often not possible, as users may have space allocated in the tempfile. Be patient, kill the sessions or restart the database
* go to the OS and remove the old_temp_file

Hi is there a need to drop the datafile?
And ... is it possible to drop a datafile without complication?
I learned that to have fewer datafiles in a Tablespace you have to recreate the whole Tablespace ?

I believe his TEMP tablespace is the true temporary tablespace - created with the "CREATE TEMPORARY TABLESPACE" command. It has tempfiles instead of datafiles and it's possible to offline and drop tempfile without any problems - if there aren't active transactions.

In a "normal" tablespace with datafiles you cannot drop a datafile without dropping the tablespace but this is another issue.

The technique I posted is the least painful I've found for "shrinking" true temporary tablespace.

AlesThe whole difference between a little boy and an adult man is the price of toys