Recovering from Loss of a Temporary Tablespace

A temporary tablespace is one used for storing “temporary” data. It is not necessary to back up temporary tablespaces, and indeed RMAN will never back them up. If you try to put a temporary tablespace into hot backup mode with ALTER TABLESPACE...BEGIN BACKUP; you will get an error. Since temporary tablespaces cannot be backed up, if damaged they cannot be restored—they must be replaced, instead.

Temporary Data

What is temporary data? It is data that exists only for the duration of one database session, possibly for less than that. It is private to the session and totally inaccessible to any other users. Temporary data usually comes in two forms: sort data and global temporary tables. Sort data is generated when an operation requiring rows to be sorted, such as the use of the ORDER BY clause in a select statement or when creating indices, cannot proceed completely Oracle Databasein memory. In an ideal world all sorts will occur in memory, but if memory is limited and the volume of data to be sorted is large, then the sort operation must process rows in batches and write out each sort run to disk before sorting the next batch. Then the multiple sort runs on disk are merged to produce a final listing of all the data, in the correct order. The sort runs are private to the session doing the sort—there is no possible reason for any other session to access them, and
indeed this is impossible.

Global temporary tables are a powerful feature of Oracle that your programmers can use. A global temporary table’s definition is visible to all sessions (hence “global”) and can be populated with rows by any and all sessions, but the rows are visible only to the session that inserted them. Many sessions can make use of the temporary table’s definition at once, but each session will see only its own rows. Global temporary tables are cleared when the session terminates. If only a few rows are inserted into a global temporary table, it will be stored in the session’s memory, but if a large amount of data is being manipulated in global temporary tables, then—just as with sort data—it will be written out to a temporary tablespace. Since temporary data persists only for the duration of the session that created it, there is no requirement to save the data permanently, or to recover it in the event of a disaster. For this reason, temporary data is not written to the regular tablespaces that store permanent objects. It is stored, for the duration of the session that created it only, in temporary segments in temporary tablespaces.

Temporary Space Configuration

To list your temporary tablespaces and their tempfiles and sizes, run this query:
SQL> select,,d.bytes from v$tablespace t, v$tempfile dwhere t.ts#=d.ts#;

An alternative query against a data dictionary view would be
SQL> select tablespace_name,file_name,bytes from dba_temp_files;

Every database will have one temporary tablespace nominated as the default temporary tablespace. This is the one used by all users who have not been specifically assigned a temporary tablespace. To identify it,
SQL> select property_value from database_properties where property_name='DEFAULT_TEMP_TABLESPACE';

Each user will have a designated temporary tablespace, possibly the database default temporary tablespace. To list them,
SQL> select username,temporary_tablespace from dba_users;

The dynamic performance view V$TABLESPACE and the data dictionary view DBA_TABLESPACES list all tablespaces, whether temporary or permanent. DBA_
TABLESPACES does, however, have a column CONTENTS that distinguishes between them.

Posted on: 12/12/2009

If you want to leave a comment please Login or Register