Oracle Temp Table Space switch

I. Temp table space role

Temporary table space is mainly used in the database for sorting operations, management index, access to the view and other operations to provide temporary computing space, when the operation is complete after the system will voluntarily clean up. WhenOracleneeds to be used.Sortthe time. PGAinsort_area_sizewhen the size is not enough, the data will be placed in the temporary table space to be sorted, at the same time assuming that there is an abnormal situation. will also be put into a temporary table space , normally speaking. In the finishedSelectstatements,CREATE INDEXand some useTEMPtable space after the sort operation,Oraclewill voluntarily release the temporary paragraph. Note the release here, but mark these spaces as spare and reusable, and the actual disk space that is occupied is not released. SoTempThe table space may become larger.

Sorting is very resource-intensive. Temp table space 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 the temporal table space:
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 ALTER 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 is complete.

three, Temp table space some frequently used queries

3.1. Change the default temporary tablespace for the system:
--Query default temporary table space
SELECT * from database_properties where property_name= ' default_temp_tablespace ';
--Change the default temporary table space
ALTER DATABASE default temporary tablespace Temp1;
--The default ephemeral 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 more resources
/* 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 and the SQL statement that is occupying 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;

< 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 Shrink (11g new functionality) for temporal tablespace
--Shrinks the temp tablespace to 20M
Alter Tablespace Temp1 shrink space Keep 20m; 
-proactively reduce the tablespace's temporary files to the smallest possible size
ALTER tablespace temp1 shrink tempfile ' .../ TEMP01.DBF ';  

Summary: The temp table space is now maintained.

Oracle Temp Table Space switch