TEMPORARY Tablespaces & TEMPFILES

Temporary tablespaces are used to manage space for database sort operations and for storing global temporary tables. Numerous SQL operations that require disk sorting such as: CREATE INDEX, ANALYZE, Select DISTINCT, ORDER BY, GROUP BY, UNION, INTERSECT, MINUS, Sort-Merge joins, etc. Thus assigning a temporary tablespace to each user in the database to prevent them from allocating sort space in the SYSTEM tablespace.

SQL>CREATE USER HRMS DEFAULT TABLESPACE Main_DB TEMPORARY TABLESPACE Temp;

SQL> ALTER USER HRMS TEMPORARY TABLESPACE temp;

TEMPFILES?

Unlike normal data files, TEMPFILE are not fully initialized. When you create a TEMPFILE, Oracle only writes to the header and last block of the file. TEMPFILEs are not recorded in the database’s control file so that one can simply re-create it after accidental delete. Temp files cannot contain permanent objects and therefore doesn’t need to be backed up.

One cannot remove datafiles from a tablespace until you drop the entire tablespace. However, one can remove a TEMPFILE from a database.

SQL> ALTER DATABASE TEMPFILE ‘C:\oracle1\oradata\temp03.dbf’ DROP INCLUDING DATAFILES;

If you remove all tempfiles from a temporary tablespace, you may get error: ORA-25153: Temporary Tablespace is Empty. To Add Tempfile:

SQL> ALTER TABLESPACE temp ADD TEMPFILE ‘C:\oracle1\oradata\temp03.dbf’ SIZE 100M;

Except for adding a tempfile you cannot use the other ALTER TABLESPACE statement for a locally managed temporary tablespace such as operations like rename, set to read only, recover, etc. will fail with tempfile.

How does one create Temporary Tablespaces?

Oracle 9i example:

SQL> CREATE TEMPORARY TABLESPACE temp

TEMPFILE ‘/oradata/mytemp_01.tmp’ SIZE 20M

EXTENT MANAGEMENT LOCAL UNIFORM SIZE 16M;

For best performance, the UNIFORM SIZE must be a multiple of the SORT_AREA_SIZE parameter. For Example: To create temporary tablespace.

SQL> CREATE TEMPORARY TABLESPACE temp;

Default Temporary Tablespaces:

In Oracle 9i and above, one can define a Default Temporary Tablespace at database creation time:

SQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp;

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 check the default temporary tablespace for a database:

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

All new users that are not explicitly assigned a TEMP TABLESPACE will get the Default Temp Tablespace as its TABLESPACE. Also, when you assign a TEMPORARY tablespace to a user, Oracle will not change this value next time you change the Default Temporary Tablespace for the database.

Other performance Considerations for Temp Tablespaces:

–         Always use temporary tablespaces instead of permanent content tablespaces for sorting operation. By using nologging and large sort segment to reduce recursive SQL and ST space management enqueue contention.

–         Always create your temporary tablespaces as locally managed instead of dictionary managed. Thus use sort space bitmap instead of sys.fet$ and sys.uet$ for allocating space.

–         Always use TEMPFILEs instead of DATAFILEs will reduce backup and recovery time as well as Prevent allocating sort space in SYSTEM tablepace.

–         Stripe your Temp tablespaces over multiple disks to minimize disk contention and to speed-up sorting operations.

Monitoring TEMP Tablespace:

Use V$TEMPFILE and DBA_TEMP_FILES to check the Tempfile.

Monitor temporary segments from V$SORT_SEGMENT and V$SORT_USAGE

Use V$TEMP_SPACE_HEADER to check free space of temporary tablespace.

Advertisements
Leave a comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: