- Create Temporary Tablespace Temp
CREATE Temporary Tablespace TEMP2 tempfile ‘d:\oracle\oradata\oradata\temp01’ SIZE 1500M, ‘d:\oracle\oradata\oradata\temp02’ SIZE 1500M;
2. Move Default Database temp tablespace
Alter database default TEMPORARY tablespace TEMP2;
3. Make sure no sessions are using your Old Temp tablespace
SQL>Select username, session_num, session_addr from v$sort_usage;
If the result set contains any rows 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 result set.
SQL> Select sid, serial#, status from v$session where serial#=session_num;
or
SQL> Select sid, serial#, status from v$session where saddr=session_addr;
Now kill the session with IMMEDIATE option or you can directly using from toad
SQL> Alter system kill ‘sid,serial#’ immediate;
4. Drop temp tablespace
SQL> drop tablespace temp including contents and datafiles;
5. Recreate Tablespace Temp
SQL> create TEMPORARY tablespace TEMP tempfile ‘D:\oracle\oradata\temp\temp01’ size 1500m;
6 Move Tablespace Temp, back to new temp tablespace
SQL> Alter database default temporary tablespace TEMP;
7. Drop temporary for tablespace temp
SQL> drop tablespace TEMP2 including contents and datafiles;
In fact there is no need to shutdown while doing these operation. If any thing happens with temp tablespace, oracle database will ignore the error, but DML and SELECT query will suffer.
