Oracle Temporary Tablespaces

Temporary Tablespaces

Using for sort operations

Cannot contain any permanent objects

Locally managed extents recommended.

Creating a Temporary Tablespace

SQL> Create Temporary Tablespace TEMP_NEW

         

          Tempfile '/u01/oracle/VIS/data/temp_new01.dbf'

          

           Size 500M;

Altering a Temporary Tablespace

You can issue the ALTER TEMPORARY TABLESPACE statement to perform various temporary tablespace management tasks, including adding a tempfile to grow a temporary tablespace. Below is an example showing how you can make the temporary tablespace larger

 How to add New temp file in temp tablespace?

 SQL> Alter tablespace TEMP_NEW

Add Tempfile '/u01/oracle/VIS/data/temp_new02.dbf'

 Size 10m;

How to resize  tempfile?

SQL> Alter Database Tempfile '/u01/oracle/VIS/data/temp_new02.dbf' Resize 100m;

How to drop tempfile?

SQL> Alter database Tempfile '/u01/oracle/VIS/data/temp_new02.dbf'

          Drop Including Datafiles;

Default Temporary Tablespace

When you create database users, you must assign a default temporary tablespace in which they can perform their temporary work, such as sorting

Eliminates Using SYSTEM tablespace for storing temporary data

Example:

SQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP_NEW;

Drop Temporary Tablespace

Default temporary tablespace cannot drop. So Assign the New Default temporary tablespace After Delete the old temp tablespace.

Error:

ORA-12906: cannot drop default temporary tablespace

SQL> Drop Tablespace TEMP_NEW Including Contents And Datafiles;

Temporary Tablespace Data Dictionary Views

dba_temp_files

V$tempfile

Frequently Using Temp Table Space Query

Find the Temporary tablespace size:

select TABLESPACE_NAME,FILE_NAME,BYTES/1024/1024,AUTOEXTENSIBLE,MAXBYTES/1024/1024 from dba_temp_files;

 

TOP Consuming queries in table space 

select * from ( select s.sid, s.status, s.sql_hash_value sesshash, u.SQLHASH sorthash, s.username, u.tablespace, sum(u.blocks*p.value/1024/1024) mbused , sum(u.extents) noexts, nvl(s.module,s.program) proginfo, floor(last_call_et/3600)||':'|| floor(mod(last_call_et,3600)/60)||':'|| mod(mod(last_call_et,3600),60) lastcallet from v$sort_usage u, v$session s, v$parameter p where u.session_addr = s.saddr and p.name = 'db_block_size' group by s.sid,s.status,s.sql_hash_value,u.sqlhash,s.username,u.tablespace, nvl(s.module,s.program), floor(last_call_et/3600)||':'|| floor(mod(last_call_et,3600)/60)||':'|| mod(mod(last_call_et,3600),60) order by 7 desc,3) where rownum < 11;

Find which sql consuming more temp

col sql_text form a80 set lines 120 select sql_text from gv$sqltext where hash_value= (select sql_hash_value from gv$session where sid=&1) order by piece /

 

Find the free space of the temp table space

 

SQL> select 'FreeSpace ' || (free_blocks*8)/1024/1024 ||' GB' from v$sort_segment where tablespace_name like 'TEMP2%';

 

 

Refer:

Master Note: Troubleshooting Oracle Temporary Tablespaces (Doc ID 1524594.1)

Note 1069041.6 How to Find Creator of a SORT or TEMPORARY SEGMENT or Users Performing Sorts

Note 317441.1 How Do You Find Who And What SQL Is Using Temp Segments