Recreate Temporary tablespace Oracle.

Below are the steps to recreate temporary tablespace.

1. To get the details of existing temporary tablespace. Run the below query.

SQL> select tablespace_name,file_name,sum(bytes/1024/1024/1024) from dba_temp_files group by tablespace_name,file_name;


TABLESPACE_NAME        FILE_NAME
------------------------------ --------------------------------------------------------
TEMPORARY                     D:\app\oracle\oradata\temporary.dbf

2. To get the space details of temporary datafiles, run the below query.

SQL> select tablespace_name,to_char(tablespace_size/1024/1024/1024),to_char(allocated_space/1024/1024/1024),to_char(free_space/1024/1024/1024) from dba_temp_free_space;

3. Create another Temporary Tablespace.

SQL> CREATE TEMPORARY TABLESPACE TEMP TEMPFILE  'D:\app\oracle\oradata\temporary.dbf' SIZE 1g autoextend on;

4. Set the newly created temporary tablespace as default.

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP;

5. Verify the dafault temporary tablespace.

SQL> select property_name,property_value from database_properties where property_name like '%TEMP%';

6. Check if any sessions are using the temporary tablespace by running below command. If sessions are there then you need to kill all of them.

SELECT b.tablespace,b.segfile#,b.segblk#,b.blocks,a.sid,a.serial#,
a.username,a.osuser, a.status
FROM v$session a,v$sort_usage b
WHERE a.saddr = b.session_addr;

ALTER SYSTEM KILL 'SID,SERIAL#' IMMEDIATE;

7. Drop the original/old temporary tablespace.

DROP TABLESPACE temporary INCLUDING CONTENTS AND DATAFILES;

Run the 5th step to check weather the old temporary tablespace is present.

 

If you want have temporary tablespace name as 'TEMPORARY' then follow the below steps.

1. Create TEMPORARY tablespace.

CREATE TEMPORARY TABLESPACE TEMPORARY TEMPFILE 'D:\app\oracle\oradata\temporary.dbf' SIZE 1g autoextend on;

2. Set this newly created temporary tablespace as Default.

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temporary;

3. Drop temporary for tablespace temp.

DROP TABLESPACE temp INCLUDING CONTENTS AND DATAFILES;