All you need to know about Temporary Segments   July 26, 2009

The oracle temporary segment and the associated tablespace manipulation differ a bit than normal tablespace.

The following discussion is to summarize issues related to the temporary segment

Certain database operations require temporary space to complete their functionality like

Create Index

Order By

Group By

Union – Intersect – Minus

Distinct

Sort Merge join etc…

The usage of the temporary space is managed internally by the database engine while the user is not aware of such usage.  In order to properly manage temporary space, Oracle maintains a segment type called Temporary Segment.  Prior to Oracle 8i, temporary segment was just a normal segment and resides on a normal tablespace.  However, more recent releases of Oracle deal with the temporary segment in a unique way.

The temporary tablespace is there to prevent temporary data from residing in other tablespace that are design to hold production data.  Additionally temporary data is only temporary and therefore get generated and purged when the operation requiring it finishes

Each database user, therefore, is required to be assigned to a temporary tablespace

How to Create a temporary tablespace

SQL> CREATE TEMPORARY TABLESPACE temp

      TEMPFILE '/u1/ammar/temp.dbf' SIZE 200M

      EXTENT MANAGEMENT LOCAL UNIFORM SIZE 20M;

Or just

SQL> CREATE TEMPORARY TABLESPACE temp;

(NOTE,  No permanent segments are permitted on a temporary tablespace)

Now, you can assign a user to the temporary tablespace using

SQL>ALTER USER SAJDI TEMPORARY TABLESPACE TEMP

A nice feature that was introduced in 9i  allows you to define a DEFAULT temporary segments that helps to avoid having your temporary activities on the SYSTEM tables   (a common bad practice). By default for all oracle database releases I know, the default temporary tablespace is SYSTEM.  It is now possible to change the default

SQL>ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp;

Now when you create a user without explicitly assigning this user to a temporary tablespace, it will by default assume the default temporary tablespace (temp in our example)

The default Default Temporary Tablespace is SYSTEM. Each database can be assigned one and only one Default Temporary Tablespace. Using this feature, a Temporary Tablespace is automatically assigned to users. The following restrictions apply to default temporary tablespaces:

- The Default Temporary Tablespace must be of type TEMPORARY

- The DEFAULT TEMPORARY TABLESPACE cannot be taken off-line

- The DEFAULT TEMPORARY TABLESPACE cannot be dropped until you create another one.

To see the default temporary tablespace for a database, execute the following query:

SQL> SELECT * FROM DATABASE_PROPERTIES where PROPERTY_NAME='DEFAULT_TEMP_TABLESPACE';

All new users who are not explicitly assigned a TEMPORARY TABLESPACE, will get the Default Temporary Tablespace as their TEMPORARY TABLESPACE.

Some performance considerations for temporary tablespaces:

- Always use temporary tablespaces instead of permanent content tablespaces for sorting (no logging and uses one large sort segment to reduce recursive SQL).

- Ensure that you create your temporary tablespaces as locally managed instead of dictionary managed

- Use TEMPFILEs instead of DATAFILEs (reduces backup and recovery time)

Monitoring Temporary Tablespaces and Sorting:

 Use V$TEMPFILE and DBA_TEMP_FILES

 V$SORT_SEGMENT and V$SORT_USAGE

Note :DBA_FREE_SPACE does not record free space for temporary tablespaces.  Use V$TEMP_SPACE_HEADER