Oracle Database - Temporary Tablespace

Card Puncher Data Processing

About

The temporary tablespace (by default TEMP with the data file TEMP01.DBF) stores temporary tables and indexes created during the processing of your SQL statement. If you are running a SQL statement that involves a lot of sorting, such as the constructs GROUP BY, ORDER BY, or DISTINCT, then you may need to expand this tablespace.

A temporary tablespace contains schema objects only for the duration of a session. Objects in temporary tablespaces are stored in tempfiles.

Temporary-Only

When you specify a “temporary” tablespace, the tablespace cannot be used to hold any permanent segments; it can only be used for temporary segments created during queries. The first sort to use the temporary tablespace allocates a temporary segment within the temporary tablespace; when the query completes, the space used by the temporary segment is not dropped. Instead, the space used by the temporary segment is available for use by other queries; this allows the sorting operation to avoid the costs of allocating and releasing space for temporary segments. This will reduce the temporary segment lock allocation contention and will save the cost of the 10 INSERT/UPDATE/DELETE statements involved in temporary segment allocation.

To dedicate a tablespace as a temporary tablespace, specify TEMPORARY in the CREATE TABLESPACE or ALTER TABLESPACE command. The example shown in the following listing changes an existing tablespace named TEMP_1 to a TEMPORARY tablespace.

alter tablespace TEMP_1 temporary;

To change the TEMP_1 tablespace out of TEMPORARY state, and allow permanent objects to be stored in it, use the PERMANENT keyword in the ALTER TABLESPACE command, as shown in the following listing.

alter tablespace TEMP_1 permanent;





Discover More
Card Puncher Data Processing
Oracle Database - Data Files

Every Oracle database has one or more physical datafiles (OS File), which contain all the database data. The data of logical database structures, such as tables and indexes, is physically stored in the...
Card Puncher Data Processing
Oracle Database - How to detect read from the temporary tablespace ?

How to detect read from the temporary tablespace ? Step followed : disable automatic PGA management by the server set the hash area size. and play with the sort area size In this example, the...
Card Puncher Data Processing
Oracle Database - Physical Read

The physical read statistic from the autotrace statistics is a measure of how much real I/O, or physical i/o, your query performed. A physical read of table or index data places the block into the buffer...
Card Puncher Data Processing
Oracle Database - TableSpace

A tablespace is a logical data structure which is physically represented by one or more datafiles physically. From a analysis point of view, a tablespace is a group of one or more: physic datafile....
Card Puncher Data Processing
Oracle Database - Temporary Datafiles (tempfiles)

Tempfiles are a special class of datafiles that are associated only with temporary tablespaces. Locally managed temporary tablespaces have temporary datafiles (tempfiles), which are similar to ordinary...
Card Puncher Data Processing
Oracle Database - sorts (Disk)

sorts (Disk) is a statistic metric of autotrace that you can see in the autotrace statistics and confirm that Oracle use the temporary tablespace.



Share this page:
Follow us:
Task Runner