Role of temporary tablespace in Oracle

Oracle temporary tablespace is mainly used to query and store some buffer data. Temporary tablespace consumption is mainly caused by sorting the intermediate query results. Restart the database

Oracle temporary tablespace is mainly used to query and store some buffer data. Temporary tablespace consumption is mainly caused by sorting the intermediate query results. Restart the database

Oracle temporary tablespace is mainly used to query and store some buffer data. Temporary tablespace consumption is mainly caused by sorting the intermediate query results. The temporary tablespace can be released when the database is restarted. If the instance cannot be restarted and the SQL statement is executed, the temp tablespace will continue to grow.

Oracle temporary tablespace is mainly used to query and store some buffer data. Temporary tablespace consumption is mainly caused by sorting the intermediate query results.

Restarting the database can release temporary tablespace. If the instance cannot be restarted, the temp tablespace will continue to grow as the problematic SQL statement is executed. Until the hard disk space is exhausted.

Someone on the Internet speculated that oracle uses the greedy algorithm to allocate disk space. If the disk space consumption reached 1 GB last time, the temporary tablespace would be 1 GB. That is to say, the size of the temporary tablespace file is the largest in history.

The main functions of temporary tablespace:

Index create or rebuild

Order by or group

Distinct operation

Union, intersect, or minus

Sort-merge joins

Analyze

View the temporary tablespace size

View the temporary table file size and used space

Select t1. "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
(
Select tablespace_name "Tablespace", to_char (sum (bytes/1024/1024/1024), '123') "Total (G )"
From dba_temp_files
Groupby tablespace_name
Union
Select tablespace_name "Tablespace", to_char (sum (bytes/1024/1024/1024), '123') "Total (G )"
From dba_data_files
Where tablespace_name like 'temp %'
Groupby tablespace_name
) T1,
(
Selecttablespace, round (sum (blocks) * 8/1024) "Used (G)" from v $ sort_usage
Groupbytablespace
) T2
Where t1. "Tablespace" = t2.tablespace (+)

SQL statement for viewing the space used by the current dead table and the space occupied by the temporary tablespace

Select sess. SID, segtype, blocks * 8/1000 "MB", SQL _text
From v $ sort_usage sort, v $ session sess, v $ SQL
Wheresort. SESSION_ADDR = sess. SADDR
Andsql. ADDRESS = sess. SQL _ADDRESS
Orderby blocks desc;

Select 'the '| name | 'temp tablespace' | tablespace_name |
'Idle' |
Round (100-(s. tot_used_blocks/s. total_blocks) * 100, 3) |
'% At' | to_char (sysdate, 'yyyymmddhh24mis ')
From (select d. 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 (+)
Groupby d. tablespace_name) s,
V $ database;

Modify temporary file size

Select 'alter database tempfile' | file_name | 'resize 100 M ;'
From dba_temp_files
Where tablespace_name = 'onlydwtemp ';

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

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

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

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

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

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

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

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

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

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

SQL> ALTER database TEMPFILE '/oradata/onlydwtemp09.dbf' resize 100 M;

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

The ORA-03297: file contains used data beyond requested RESIZE value

Create a new temporary tablespace

SQL> create temporary tablespace TEMP1 TEMPFILE '/oradata/temp1_01.dbf' size 100 M;

Tablespace created

SQL> create temporary tablespace TEMP2 TEMPFILE '/oradata/temp2_01.dbf' size 100 M;

Tablespace created

Specify the current temporary tablespace as the new temporary tablespace.

SQL> alter database default temporary tablespace TEMP1;

Database altered

,