Cleanup of temporary table spaces in Oracle

Iamlaosong

The Oracle Temp table space is used primarily for querying and storing some buffer data. The primary reason for the temporary tablespace consumption is the need to sort the intermediate results of the query. The primary role of temporal tablespace:

index Create or rebuild
Order by or GROUP by
Distinct operation
Union or intersect or minus
Sort-merge joins
Analyze

Restarting the database frees up the temporary tablespace, and if the instance cannot be restarted, the temp table space will continue to grow as the problem SQL statement is executed. Even after a temporary tablespace is rebuilt, the temporary table space is used for 99% of the time, and then the tablespace begins to grow until it runs out of hard disk space. As shown in the temporary table space, just established is 8G, and now has reached 32G:

In order to prevent the increase in the temporal tablespace, I use the method of rebuilding the temporal table space at intervals, for convenience, I keep two sets of statements, alternately, assuming that the temporary tablespace name is temp, create a new Tempa table space, and delete the temp table space, by doing the following:

Create temporary tablespace tempa tempfile '/opt/app/oracle/oradata/orcl/tempa01.dbf ' SIZE 8192M reuse   Autoextend on NEXT 1024K MAXSIZE UNLIMITED; --Create transit temporary table space
ALTER DATABASE default temporary tablespace Tempa; --Change the default temp table space

Drop Tablespace temp including contents and datafiles; --Delete the original temporary table space

The new temporary tablespace is as follows:

After a while, when the temporal table space grows to a certain extent, create a new temp tablespace and delete the Tempa table space, which is:

Create temporary tablespace TEMP tempfile '/opt/app/oracle/oradata/orcl/temp01.dbf ' SIZE 8192M reuse AU   Toextend on NEXT 1024K MAXSIZE UNLIMITED; --Create transit temporary table space
ALTER DATABASE default temporary tablespace temp; --Change the default temp table space
Drop tablespace Tempa including contents and datafiles; --Delete the original temporary table space

This will ensure that the temporary table space is not too large to prevent excessive use of limited hard disk space.

=====================================================

Use the following statement to view the current temporal tablespace space usage and the SQL statement that is occupying the temporary tablespace:

Select Sess. SID, Segtype, blocks * 8/1000 "MB", Sql_text
From V$sort_usage sort, v$session sess, V$sql sql
where sort. SESSION_ADDR = Sess. Saddr
and SQL. ADDRESS = Sess. Sql_address
ORDER BY blocks Desc;

The following statement queries for the degree of inactivity of the temporary table space:

Select ' The ' | | name | | ' Temp tablespaces ' | | Tablespace_name | |
' Idle ' | |
Round (s.tot_used_blocks/s.total_blocks) * 100, 3) | |
'% at ' | | To_char (sysdate, ' Yyyymmddhh24miss ')
From (select D.tablespace_name tablespace_name,
NVL (sum (used_blocks), 0) Tot_used_blocks,
SUM (blocks) total_blocks
From V$sort_segment V, dba_temp_files D
where D.tablespace_name = V.tablespace_name (+)
Group by D.tablespace_name) s,
V$database;

Cleanup of temporary table spaces in Oracle