Oracle Temp Table Space switch

I. Temp table space role

The primary purpose of the temporary tablespace is to provide temporary computing space when the database is sorted, manage indexes, access views, and so on, and the system will automatically clean up when the operation is complete. WhenOracleneeds to be used.Sortthe time,PGAinsort_area_sizewhen the size is not enough, the data will be placed in the temporary table space for sorting, and if there is an exception, it will be placed in the temporary table space , normally, at the completionSelectstatements,CREATE INDEXand some useTEMPtable space after the sort operation,Oracleit will automatically release the temporary segment. Note that the release here simply marks the space as free and reusable, and the actual disk space that is occupied is not released. SoTempThe table space may become larger.

Sorting is very resource-intensive, the Temp table is full , the key is to optimize your statement, as far as possible to reduce the ranking is the best policy .

Summarized as:

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.

Second, Oracle temp table Space switch

< Span style= "Color:rgb (51,51,51); font-family: ' Helvetica Neue ', Helvetica,tahoma,arial,stxihei, ' Microsoft Yahei ', Microsoft Ya Hei, Sans-serif; line-height:27.1875px; Background-color:rgb (254,254,254) ">2.1 Query temp Table space usage:

select Temp_used.tablespace_name,
       total-used as" Free,
       total as "Total",
       round (NVL (total-used, 0) * 100/ Total, 3) ' free percent '
  from ( select tablespace_name, SUM (bytes_used)/1024/1024 used
  &NBS P         from Gv$temp_space_header
        GROUP by Tablespace_name) temp_used,< br>        ( select tablespace_name, SUM (bytes)/1024/1024 Total
      &NBS P     from Dba_temp_files
        GROUP by Tablespace_name) temp_total
 where Temp _used.tablespace_name = Temp_total.tablespace_name;

< Span style= "Color:rgb (51,51,51); font-family: ' Helvetica Neue ', Helvetica,tahoma,arial,stxihei, ' Microsoft Yahei ', Microsoft Ya Hei, Sans-serif; line-height:27.1875px; Background-color:rgb (254,254,254) ">

< Span style= "Color:rgb (51,51,51); font-family: ' Helvetica Neue ', Helvetica,tahoma,arial,stxihei, ' Microsoft Yahei ', Microsoft Ya Hei, Sans-serif; line-height:27.1875px; Background-color:rgb (254,254,254) "> database default tablespace:

< Span style= "Color:rgb (51,51,51); font-family: ' Helvetica Neue ', Helvetica,tahoma,arial,stxihei, ' Microsoft Yahei ', Microsoft Ya Hei, Sans-serif; line-height:27.1875px; Background-color:rgb (254,254,254) ">sql> Select Property_name, Property_value
  2    from Database_properties
  3   WHERE property_name = ' default_temp_tablespace ';

2.2 new TEMP2 table space

< Span style= "Color:rgb (51,51,51); font-family: ' Helvetica Neue ', Helvetica,tahoma,arial,stxihei, ' Microsoft Yahei ', Microsoft Ya Hei, Sans-serif; line-height:27.1875px; Background-color:rgb (254,254,254) ">create temporary tablespace temp1 tempfile   ' D:\APP\ADMINISTRATOR\ORADATA\ sdxj\temp2.dbf ' size 20M autoextend on next 1M MaxSize unlimited
tablespace Group '
Extent management Local uniform Size 1M;

2.3 Modifying the database default tablespace is TEMP1

sql> ALTER DATABASE default temporary tablespace Temp1;
The database has changed.

2.4 Deleting the original table space

Exit session, delete the original table space

DROP tablespace TEMP including CONTENTS and datafiles;

Delete complete.

three, Temp table space Some common queries

3.1. Change the system's default temporary tablespace:
--Query the default temporary table space
SELECT * from database_properties where property_name= ' default_temp_tablespace ';
--Modify the default temp table space
ALTER DATABASE default temporary tablespace Temp1;
--The default temporary tablespace for all users is switched to the new temporary tablespace:
Select Username,temporary_tablespace,default_ from Dba_users;
--Change the temporary tablespace for a user:
Alter user Scott temporary tablespace temp;

3.2 Finding SQL statements that consume resource comparisons
/* Formatted on 2015/7/14 21:58:17 (QP5 v5.163.1008.3004) */
SELECT Se.username,
Se.sid,
Su.extents,
Su.blocks * To_number (RTRIM (P.value)) as Space,
Tablespace,
Segtype,
Sql_text
From V$sort_usage Su,
V$parameter p,
V$session SE,
V$sql s
WHERE p.name = ' db_block_size '
and su.session_addr = Se.saddr
and S.hash_value = Su.sqlhash
and s.address = Su.sqladdr
ORDER by Se.username, Se.sid

3.3. View the current temporary tablespace usage size vs. the SQL statement that is occupying the temp 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;

< Span style= "Color:rgb (51,51,51); font-family: ' Helvetica Neue ', Helvetica,tahoma,arial,stxihei, ' Microsoft Yahei ', Microsoft Ya Hei, Sans-serif; line-height:27.1875px; Background-color:rgb (254,254,254) "> 4.4 for temporary tablespace shrink (11g new feature)
--Shrinks the temp tablespace to 20M
Alter Tablespace Temp1 shrink space Keep 20m; 
-Automatically shrinks the tablespace's temporary files to the smallest possible size
ALTER tablespace temp1 shrink tempfile ' .../temp01.dbf ';  

Summary: The temp table space maintenance is complete.

Copyright NOTICE: This article for Bo Master original article, without Bo Master permission not reproduced.

Oracle Temp Table Space switch