Create new TEMPORARY tablespace
                                                 Last update (2012-06-08 20:36:52)
                                                                                                                   Date added (2008-02-11 12:48:32)

Summary
Sometimes the temporary tablespace grows so much that you want to take back the occupied space but you cannot.

One solution is to "drop" the temporary tablespace and create a new one with the desired space. You can safely drop a temporary tablespace in most cases since they are not stored permanent objects in it.

How to drop the temporary tablespace and create a new one.
1. Find the users that have the temporary tablespace that you will drop as "default temporary"
SELECT 'ALTER USER ' || USERNAME || ' TEMPORARY TABLESPACE ' || 'NEW_TEMP;'
FROM DBA_USERS WHERE temporary_tablespace = 'OLD_TEMPORARY_TABLESPACE';
Save output as a script, lets say change_temporary_tablespace.sql

2. Create the new temporary tablespace
SQL>CREATE TEMPORARY TABLESPACE NEW_TEMP TEMPFILE '/ora2/MISPROD/datafiles/temp_01.dbf' SIZE 500M;
3. Run the script change_temporary_tablespace.sql to set the NEW_TEMP as "default temporary" tablespace for the users that have as a temporary this that you will drop.
sqlplus>@change_temporary_tablespace.sql;
4. In case you are in Oracle9i and above and the temporary tablespace that you will drop is "database default temporary", change the DEFAULT TEMPORARY TABLESPACE of the database to the new one.
SQL>ALTER "DATABASE" DEFAULT TEMPORARY TABLESPACE NEW_TEMP;
5. Now you can drop the old temporary tablespace.
SQL>DROP TABLESPACE old_temporary_tablespace INCLUDING CONTENTS AND DATAFILES;
Tip:
In case sessions are using the old temporary tablespace (v$sort_usage), then the "drop tablespace" hangs for ever with enqueue contention at the TS. To bypass the problem you can drop first the tempfiles and afterwards the temporary tablespace.
ALTER "DATABASE" TEMPFILE '/ora2/MISPROD/datafiles/temp_old_01.dbf' DROP INCLUDING DATAFILES;
DROP TABLESPACE old_temporary_tablespace;
Other links
Create new control file
Create new UNDO tablespace
Move or rename datafiles
Move or rename System datafiles
Reviews
Categories
Oracle DBA-> (147)
  Alertlog (3)
  Archivelog (5)
  Auditing (1)
  Backup - Recovery (19)
  Database files (6)
  Database options (2)
  DB links (2)
  Dependencies (1)
  Dictionary (2)
  Exp-Imp Datapump (6)
  Jobs (2)
  Mview (2)
  Networking (3)
  Objects (7)
  Parameters (2)
  Redologs (6)
  Roles - Grants (2)
  Rollback - Undo (8)
  Segments (3)
  Sequences (2)
  Sessions (14)
  SGA (14)
  Tables (10)
  Tablespaces (10)
  Temp (4)
  Toad (5)
  Transactions (1)
  Upgrade (2)
  Users (3)
Oracle APPS DBA-> (66)
Exadata (1)
Performance Tuning-> (59)
Oracle Real Cases (24)
Oracle Errors (23)
Oracle SQL tricks (32)
Oracle RAC (3)
Oracle Security (8)
Filters
Search