How to drop and create a Temporary Tablespace
Temporary tablespaces are used to manage space for database sort operations and for storing global temporary tables. For example, if you join two large tables, and Oracle cannot do the sort in memory (see SORT_AREA_SIZE initialization parameter), space will be allocated in a temporary tablespace for doing the sort operation. Other SQL operations that might require disk sorting are: CREATE INDEX, ANALYZE, Select DISTINCT, ORDER BY, GROUP BY, UNION, INTERSECT, MINUS, Sort-Merge joins, etc.
1. Create another Temporary Tablespace
CREATE TEMPORARY TABLESPACE TEMP2TEMPFILE ‘/xxx/xxx/temp02′ SIZE 500m;
2. Change default Database temporary tablespace
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP2;
3. Make sure No sessions are using your Old Temp tablespace
SELECT USERNAME, SESSION_NUM, SESSION_ADDR FROM V$SORT_USAGE;
If you find some sort of operation in progress, wait until the terms or manually end the process
USERNAME SESSION_NUM SESSION_ADDR
—————————— ——————— —————————
SYS 65 000007FF646EDE68
4. Drop old tablespace temp
DROP TABLESPACE TEMP1 INCLUDING CONTENTS AND DATAFILES;
5. Recreate tablespace temp1
CREATE TEMPORARY TABLESPACE TEMP1 TEMPFILE /xxx/xxx/temp01′ SIZE 500M;
6. Move Tablespace Temp, back to new temp tablespace
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP1;
7. Drop tablespace temp2 because default temporary tablespace is temp1
DROP TABLESPACE TEMP2 INCLUDING CONTENTS AND DATAFILES;