Oracle:How to create new Temp Tablespace

If you have your Oracle Temp Tablespace set to Auto Grow, you would have noticed TEMP tablespace becoming quite large

On my database , it was around 20GB and I decided to create a new Temp Tablespace and drop the old one.

Log in as SYS

Create a new Temp tablespace TEMP2

Create Temporary tablespace TEMP2 TEMPFILE 'D:\oracle\product\10.2.0\oradata\suppdb\TEMP.DBF' SIZE 500M AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL;

Make the new Temp tablespace as the default

Alter database default temporary tablespace Temp2;

You can verify it by the SQL below

SELECT Distinct temporary_tablespace from dba_users ;



Now time to drop the old one, You need to first verify if any existing sessions is still using your old TEMP tablespace

SELECT u.USERNAME, u.SESSION_NUM, u.SESSION_ADDR, s.sid,s.serial#,s.schemaname,s.osuser,
s.program,'Alter System Kill Session '''||s.sid||','||s.serial#||''';' Kill_Session
FROM V$SORT_USAGE u, v$session s
where u.session_addr = s.saddr;


You can either kill the sessions, are ask the corresponding users to close out their connection. Once all existing connections are closed/killed, you can drop the old temp tablespace.

 Drop tablespace Temp including contents and datafiles;


Finally Rename the new tablespace from TEMP2 to TEMP

Alter tablespace TEMP2 rename to TEMP;

You can verify it by the SQL below

SELECT Distinct temporary_tablespace from dba_users ;