An instance of releasing a temporary tablespace

Freeing temporary table space instances

Temporary table space

temporary table space function

The Oracle temporary table space is used primarily for querying and storing buffer data. The primary reason for temporary table space consumption is the need to sort the intermediate results of a query.

Restarting a database frees up temporary tablespaces, and if you can't restart the instance and keep the problem SQL statement up and down, the temp table space grows. Until you run out of hard disk space.

Online speculation on the allocation of disk space, Oracle is using a greedy algorithm, if the last disk space consumption reached 1GB, then the temporary table space is 1GB. That is, the size of the current temporary tablespace file is the maximum size of a temporary table space used historically.

Primary role of temporary tablespace:

index Create or rebuild

Order BY or GROUP by

Distinct operation

Union or intersect or minus

Sort-merge joins

Analyze View Temp table space size

View temporary table file size and used space

SelectT1. " Tablespace "Tablespace",
T1. " Total (g) "Total (g)",
NVL (T2.) Used (g) ", 0)" Used (g) ",
T1. " Total (G) "-NVL (T2.") Used (g) ", 0" free (g) "
from
(
SelectTablespace_name "Tablespace", To_char (sum(bytes/1024/1024/1024)), ' 99,999,990.900 ') "Total (G)"
fromDba_temp_files
Group byTablespace_name
Union
SelectTablespace_name "Tablespace", To_char (sum(bytes/1024/1024/1024)), ' 99,999,990.900 ') "Total (G)"
fromDba_data_files
whereTablespace_name like' temp% '
Group byTablespace_name
) T1,
(
Select tablespace, round (sum(blocks) *8/1024) "Used (G)" fromV$sort_usage
Group by tablespace
) T2
whereT1. " Tablespace "=t2.tablespace(+)

View the current mortal table using the space size and SQL statements that are taking up the temporary table space

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;

Select' The ' | |name|| ' Temp tablespaces ' | | Tablespace_name | |
' Idle ' | |
Round (s.tot_used_blocks/s.total_blocks) * 100, 3) | |
'% at ' | | To_char (sysdate, ' Yyyymmddhh24miss ')
from(SelectD.tablespace_name Tablespace_name,
NVL (sum(Used_blocks), 0) Tot_used_blocks,
sum(blocks) total_blocks
fromV$sort_segment V, dba_temp_files D
whereD.tablespace_name = V.tablespace_name (+)
Group byD.tablespace_name) S,
V$database;Modify temporary file size

Select ' ALTER database Tempfile ' | | file_name | | ' Resize 100M; '
from Dba_temp_files
where tablespace_name = ' onlydwtemp ';

ALTER database tempfile '/oradata/onlydwtemp06.dbf ' resize 100M;

ALTER database tempfile '/oradata/onlydwtemp07.dbf ' resize 100M;

ALTER database tempfile '/oradata/onlydwtemp08.dbf ' resize 100M;

ALTER database tempfile '/oradata/onlydwtemp09.dbf ' resize 100M;

ALTER database tempfile '/oradata/onlydwtemp10.dbf ' resize 100M;

ALTER database tempfile '/oradata/onlydwtemp01.dbf ' resize 100M;

ALTER database tempfile '/oradata/onlydwtemp02.dbf ' resize 100M;

ALTER database tempfile '/oradata/onlydwtemp03.dbf ' resize 100M;

ALTER database tempfile '/oradata/onlydwtemp04.dbf ' resize 100M;

ALTER database tempfile '/oradata/onlydwtemp05.dbf ' resize 100M;

sql> ALTER database tempfile '/oradata/onlydwtemp09.dbf ' resize 100M;

ALTER database tempfile '/oradata/onlydwtemp09.dbf ' resize 100M

Ora-03297:file contains used data beyond requested RESIZE value creates a new temporary table space

sql> Create temporary tablespace TEMP1 tempfile '/oradata/temp1_01.dbf ' size 100M;

Tablespace created

sql> Create temporary tablespace TEMP2 tempfile '/oradata/temp2_01.dbf ' size 100M;

Tablespace created specifies the current temporary table space as a new temporary tablespace

sql> ALTER DATABASE default temporary tablespace TEMP1;

Database altered Delete old temporary table spaces

Sql> drop tablespace onlydwtemp including contents and datafiles;

Tablespace dropped Check temporary table space for the current user

9i before, if a database user is not assigned a default temporary tablespace, Oracle will use the system tablespace as a temporary tablespace for that user, which is dangerous. In 9i, the database can be assigned a default temporary tablespace. In this way, if the database user is not explicitly assigned a temporary tablespace, Oracle9i automatically specifies the default temporary tablespace for the DB as the temporary tablespace for that user.

We can query the default temp table space for the database by using the following statement.

Select * from