Recreate Temp Tablespace In Oracle Database

In this tutorial we are going to learn about recreate temporary tablespace in oracle database.

What is temporary tablespace?

Temporary tablespace is a special set of files that are user to manage space for database sorting and joining operations and for storing global temporary tables. When sorting large result set or joining large tables oracle database cannot do it in memory by using SORT_AREA_SIZE in PGA. Space will be allocated in a temporary tablespace for doing these types of operations. 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.

Note: Temporary tablespace contains schema objects only for duration of a session and temporary tablespace cannot contain permanent objects as a result we don’t need to take backup temporary tablespace.

Temporary tablespace recreate steps are given below

Step 1: Check existing temporary tablespace name, location and data file name

SQL> set lines 200
SQL> col TABLESPACE_NAME for a8
SQL> col FILE_NAME for a60
SQL> select tablespace_name,file_name from dba_temp_files;

TABLESPA FILE_NAME
-------- ------------------------------------------------------------
TEMP     E:\APP\MASUD\VIRTUAL\ORADATA\TEST\TEMP01.DBF

Step 2: Create another temporary tablespace (TEMP1) using below command

SQL> CREATE TEMPORARY TABLESPACE TEMP1 TEMPFILE 'E:\APP\MASUD\VIRTUAL\ORADATA\TEST\TEMP02.DBF' SIZE 2G;

Tablespace created.

Note: Temporary tablespace size define based on the usage and you can use auto extend when you create temporary tablespace as well as you can add data file into the temporary tablespace as requirement.

Step 3: Default permanent temporary tablespace check using below command

SQL> select PROPERTY_NAME,PROPERTY_VALUE  from database_properties where PROPERTY_NAME like '%TABLESPACE%';

PROPERTY_NAME                                                     PROPERTY_VALUE

------------------------------ ------------------------------------------------------------

DEFAULT_PERMANENT_TABLESPACE                 USERS

DEFAULT_TEMP_TABLESPACE                               TEMP

Step 4: Now newly created temporary tablespace make to the default temporary tablespace

SQL> alter database default temporary tablespace temp1;

Database altered.

After executing default temporary tablespace go to step 3 to check default temporary tablespace name change from TEMP to TEMP1.

Step 5: Check session using temp tablespace

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

TABLESPACE                       SEGFILE#    SEGBLK#     BLOCKS        SID    SERIAL#
------------------------------ ---------- ---------- ---------- ---------- ----------
USERNAME
------------------------------------------------------------------------------------------
OSUSER                                                                                                                         
-------------------------------------------------------------------------------------------
TEMP                                  201      27392        128          7      37843

OracleServiceTEST                                                                                                             


Step 6:Kill the session that are using TEMP tablespace using below command

SQL> alter system kill session '7,37843'; (7=SID,37843=SERIAL#)

Step 7: Drop the old temporary tablespace using below command which will delete contents and data files also

SQL> drop tablespace temp including contents and datafiles;

Tablespace dropped.

If you want to change the name from TEMP1 to TEMP then follow same steps mention above to recreate temp tablespace with old name.