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. 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 rebuildOrder by or groupDistinct operationUnion, intersect, or minusSort-merge joinsAnalyzeView the temporary tablespace sizeView the temporary table file size and used spaceSelect 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 tablespaceSelect 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 sizeSelect '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 MThe ORA-03297: file contains used data beyond requested RESIZE valueCreate a new temporary tablespaceSQL> create temporary tablespace TEMP1 TEMPFILE '/oradata/temp1_01.dbf' size 100 M;Tablespace createdSQL> create temporary tablespace TEMP2 TEMPFILE '/oradata/temp2_01.dbf' size 100 M;Tablespace createdSpecify the current temporary tablespace as the new temporary tablespace.SQL> alter database default temporary tablespace TEMP1;Database altered