How to drop and recreate TEMP Tablespace in Oracle 9i/10g/11g
How to drop and recreate TEMP Tablespace in Oracle 9i/10g/11g
1. Create Temporary Tablespace Temp
CREATE TEMPORARY TABLESPACE TEMP2 TEMPFILE '/u01/app/oracle/oradata/bidb/tempX.dbf' SIZE 2000M;
2. Move Default Database temp tablespace
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp2;
3. Make sure No sessions are using your Old Temp tablespace
a. Find Session Number from V$SORT_USAGE:
SELECT USERNAME, SESSION_NUM, SESSION_ADDR FROM V$SORT_USAGE;
b. Find Session ID from V$SESSION:
If the resultset contains any tows then your next step will be to find the SID from the V$SESSION view. You can find session id by using SESSION_NUM or SESSION_ADDR from previous resultset.
SELECT SID, SERIAL#, STATUS FROM V$SESSION WHERE SERIAL#=SESSION_NUM;
OR
SELECT SID, SERIAL#, STATUS FROM V$SESSION WHERE SADDR=SESSION_ADDR;
c. Kill Session:
Now kill the session with IMMEDIATE.
ALTER SYSTEM KILL 'SID,SERIAL#' IMMEDIATE;
4. Drop temp tablespace
DROP TABLESPACE temp INCLUDING CONTENTS AND DATAFILES;
5. Recreate Tablespace Temp
CREATE TEMPORARY TABLESPACE TEMP TEMPFILE '/u01/app/oracle/oradata/bidb/temp01.dbf' size 500M reuse AUTOEXTEND ON NEXT 100M MAXSIZE unlimited, '/u01/app/oracle/oradata/bidb/temp02.dbf' size 500M reuse AUTOEXTEND ON NEXT 100M MAXSIZE unlimited;
6 Move Tablespace Temp, back to new temp tablespace
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp;
7. Drop temporary for tablespace temp
DROP TABLESPACE temp2 INCLUDING CONTENTS AND DATAFILES;
No need to do shutdown when drop temp tablespace and the recreate it. If something happen with temp tablespaces e.g. : crash, corrupt, etc. Oracle database will ignore the error, but DML (insert,update,delete) and SELECT Query will suffer.
1. Create Temporary Tablespace Temp
CREATE TEMPORARY TABLESPACE TEMP2 TEMPFILE '/u01/app/oracle/oradata/bidb/tempX.dbf' SIZE 2000M;
2. Move Default Database temp tablespace
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp2;
3. Make sure No sessions are using your Old Temp tablespace
a. Find Session Number from V$SORT_USAGE:
SELECT USERNAME, SESSION_NUM, SESSION_ADDR FROM V$SORT_USAGE;
b. Find Session ID from V$SESSION:
If the resultset contains any tows then your next step will be to find the SID from the V$SESSION view. You can find session id by using SESSION_NUM or SESSION_ADDR from previous resultset.
SELECT SID, SERIAL#, STATUS FROM V$SESSION WHERE SERIAL#=SESSION_NUM;
OR
SELECT SID, SERIAL#, STATUS FROM V$SESSION WHERE SADDR=SESSION_ADDR;
c. Kill Session:
Now kill the session with IMMEDIATE.
ALTER SYSTEM KILL 'SID,SERIAL#' IMMEDIATE;
4. Drop temp tablespace
DROP TABLESPACE temp INCLUDING CONTENTS AND DATAFILES;
5. Recreate Tablespace Temp
CREATE TEMPORARY TABLESPACE TEMP TEMPFILE '/u01/app/oracle/oradata/bidb/temp01.dbf' size 500M reuse AUTOEXTEND ON NEXT 100M MAXSIZE unlimited, '/u01/app/oracle/oradata/bidb/temp02.dbf' size 500M reuse AUTOEXTEND ON NEXT 100M MAXSIZE unlimited;
6 Move Tablespace Temp, back to new temp tablespace
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp;
7. Drop temporary for tablespace temp
DROP TABLESPACE temp2 INCLUDING CONTENTS AND DATAFILES;
No need to do shutdown when drop temp tablespace and the recreate it. If something happen with temp tablespaces e.g. : crash, corrupt, etc. Oracle database will ignore the error, but DML (insert,update,delete) and SELECT Query will suffer.
