Interim table space function in Oracle __oracle

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 tablespace, and if you can't restart the instance and keep the problem SQL statement executing, the temp table space is growing. The Oracle temporary table space is primarily used 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 operations Union or intersect or minus Sort-merge joins View temporary 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
GroupByTablespace_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% '
GroupByTablespace_name
) T1,
(
Selecttablespace, round ( sum(blocks) *8/1024) "Used (G)" fromV$sort_usage
Groupbytablespace
) T2
whereT1. " Tablespace "=t2. tablespace(+) View the current mortal table uses the space size and the SQL statement that is occupying the temporary table space SelectSess. SID, Segtype, blocks*8/1000 "MB", Sql_text
fromV$sort_usage Sort, V$session Sess,v$sql SQL
Wheresort. SESSION_ADDR = Sess. Saddr
Andsql. Address = Sess. Sql_address
byBlocks 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 (+)
GroupByD.tablespace_name) S,
V$database; Modify temporary file size Select' ALTER database Tempfile ' | | file_name | | ' Resize 100M; '
fromDba_temp_files
whereTablespace_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 Create a new temporary table spacesql> Create temporary tablespace TEMP1 tempfile '/oradata/temp1_01.dbf ' size 100M; Tablespace created sql> create temporary tablespace TEMP2 ' tempfile ' size 100M; Tablespace created Specifies the current temporary table space as a new temporary tablespacesql> ALTER DATABASE default temporary tablespace TEMP1; Database Altered