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:
- Create Temporary Tablespace TempNew
- Make the default tablespace to TemNew
- Drop temp tablespace TEMP
- Recreate Tablespace Temp
- Make the default tablespace to Temp
- 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>