A temporary tablespace does exist on a permanent basis as do other tablespaces, such as the System and Sysaux tablespaces. However, the data in a temporary tablespace is of a temporary nature, which persists only for the length of a user session. Oracle uses temporary tablespaces as work areas for tasks such as sort operations for users and sorting during index creation. Oracle does not allow users to create objects in a temporary tablespace. By definition, the temporary tablespace holds data only for the duration of a user’s session, and the data can be shared by all users. The performance of temporary tablespaces is extremely critical when your application uses sort- and hash-intensive queries, which need to store transient data in the temporary tablespace.

The following v$ views contain information about temporary tablespaces:

  • gv$sort_segment Use this view to explore current and maximum sort segment usage statistics.
  • gv$tempseg_usage Use this view to explore temporary segment usage details such as username, SQL, and so on.
  • v$tempfile Use this view to identify temporary datafiles being used for a temporary tablespace.

Steps:

  1. Create Temporary Tablespace TempNew
  2. Make the default tablespace to TemNew
  3. Drop temp tablespace TEMP
  4. Recreate Tablespace Temp
  5. Make the default tablespace to Temp
  6. Drop temporary tablespace TempNew

Step 1. Create Temporary Tablespace TempNew.

$ sqlplus sys as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Thu Feb 8 17:56:49 2018
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Enter password:

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

Schema       Database
-----------  -----------
SYS          LINUXPRO
SQL> CREATE TEMPORARY TABLESPACE TempNew TEMPFILE '/opt/app/oracle/oradata/linuxpro/tempnew01.dbf' SIZE 50M;

Tablespace created.

Step 2. Make the default tablespace to TemNew.

SQL>ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TempNew;SQL>

Database altered.
SQL> select username, TEMPORARY_TABLESPACE from dba_users;

USERNAME                        TEMPORARY_TABLESPACE
------------------------------ ------------------------------
SYS                             TEMPNEW
SYSTEM 				TEMPNEW
LAGOSTINI 			TEMPNEW
OUTLN 				TEMPNEW
MGMT_VIEW 			TEMPNEW
FLOWS_FILES 			TEMPNEW
MDSYS 				TEMPNEW
ORDSYS 				TEMPNEW
EXFSYS 				TEMPNEW
DBSNMP 				TEMPNEW
WMSYS 				TEMPNEW
APPQOSSYS 			TEMPNEW
APEX_030200 			TEMPNEW
OWBSYS_AUDIT 			TEMPNEW
ORDDATA 			TEMPNEW
CTXSYS 				TEMPNEW
ANONYMOUS 			TEMPNEW
SYSMAN 				TEMPNEW
XDB 				TEMPNEW
ORDPLUGINS 			TEMPNEW
OWBSYS 				TEMPNEW
SI_INFORMTN_SCHEMA 		TEMPNEW
OLAPSYS 			TEMPNEW
SCOTT 				TEMPNEW
ORACLE_OCM 			TEMPNEW
XS$NULL 			TEMPNEW
MDDATA 				TEMPNEW
DIP 				TEMPNEW
APEX_PUBLIC_USER 		TEMPNEW
SPATIAL_CSW_ADMIN_USR 		TEMPNEW
SPATIAL_WFS_ADMIN_USR 		TEMPNEW

31 rows selected.

Step 3. Drop temp tablespace TEMP.

SQL> DROP TABLESPACE temp INCLUDING CONTENTS AND DATAFILES;

Tablespace dropped.

Step 4. Recreate Tablespace Temp.

SQL> CREATE TEMPORARY TABLESPACE TEMP TEMPFILE '/opt/app/oracle/oradata/linuxpro/temp01.dbf' SIZE 50M;

Tablespace created.

Step 5. Make the default tablespace to Temp.

SQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp;

Database altered.

Step 6. Drop temporary tablespace TempNew.

SQL> DROP TABLESPACE TempNew INCLUDING CONTENTS AND DATAFILES;

Tablespace dropped.
SQL> select username, TEMPORARY_TABLESPACE from dba_users;

USERNAME 			TEMPORARY_TABLESPACE
------------------------------ ------------------------------
SYS 				TEMP
SYSTEM 				TEMP
LAGOSTINI 			TEMP
OUTLN 				TEMP
MGMT_VIEW 			TEMP
FLOWS_FILES 			TEMP
MDSYS 				TEMP
ORDSYS 				TEMP
EXFSYS 				TEMP
DBSNMP 				TEMP
WMSYS 				TEMP
APPQOSSYS 			TEMP
APEX_030200 			TEMP
OWBSYS_AUDIT 			TEMP
ORDDATA 			TEMP
CTXSYS 				TEMP
ANONYMOUS 			TEMP
SYSMAN 				TEMP
XDB 				TEMP
ORDPLUGINS 			TEMP
OWBSYS 				TEMP
SI_INFORMTN_SCHEMA 		TEMP
OLAPSYS 			TEMP
SCOTT 				TEMP
ORACLE_OCM 			TEMP
XS$NULL				TEMP
MDDATA 				TEMP
DIP 				TEMP
APEX_PUBLIC_USER 		TEMP
SPATIAL_CSW_ADMIN_USR 		TEMP
SPATIAL_WFS_ADMIN_USR 		TEMP

31 rows selected.

SQL>