Oracle Temporary Tablespace

 Temporary Tablespace Usage

Oracle uses Temp tablespace for operations like sorting and also used in join operations. A database can have multiple Temp tablespaces but only one of them can be assigned as the default tablespace.

So before getting into Temp tablespace monitoring stuff, remember you wont get that information from DBA_FREE_SPACE view. Instead use V$TEMP_SPACE_HEADER as shown below

Finding the default temporary tablespace:

select property_name,property_value from database_properties where property_name='default_temp_tablespace';

List all temporary tablespaces in the instance:

select tablespace_name, status,contents from dba_tablespaces where contents='TEMPORARY';

Create temporary tablespace:

create temporary tablespace <TEMP NAME> tempfile '/oracle/datafile/tempfile01.dbf' size 1G;

Change the default temporary tablespace of the database:

alter database default temporary tablespace <NEW TEMP NAME>;

Change the default temporary tablespace of the user:

alter user <USER NAME> default temporary tablespace <TEMP NAME>;

Find the Allocated, Used and Free MB in temporary tablespace:

      select * from (select a.tablespace_name, 
      sum(a.bytes/1024/1024) allocated_mb 
      from dba_temp_files a 
      where a.tablespace_name = upper('&&temp_tsname') group by a.tablespace_name) x, 
      (select sum(b.bytes_used/1024/1024) used_mb, 
      sum(b.bytes_free/1024/1024) free_mb 
      from v$temp_space_header b 
      where b.tablespace_name=upper('&&temp_tsname') group by b.tablespace_name); 

Enter value for temp_tsname: TEMP 

                                            (OR)

select tablespace_name,bytes_used/1024/1024 used_mb,bytes_free/1024/1024 free_mb from v$temp_space_header;

List the datafiles under the temporary tablespace:

select file_name, tablespace_name, bytes/1024/1024 size_mb,autoextensible from dba_temp_files;

Increase temporary tablespace:

             To Add: alter tablespace TEMP add tempfile '/oracle/datafile/tempfile02.dbf' size 1G;

             To Resize: alter database tempfile '/oracle/datafile/tempfile01.dbf' resize 1G;

Drop the temporary tablespace:

drop tablespace <TEMP NAME> including contents and datafiles;

 Identify who is filling up the Temporary Tablespace (ORA-1652)

 You have just noticed that the TEMP tablespace is filling up fast.

Now you want to know which user and SQL statement is causing that then use the below mentioned script

select s.sid || ',' || s.serial# sid_serial, s.username,

o.blocks * t.block_size / 1024 / 1024 mb_used, o.tablespace, s.status,

o.sqladdr address, h.hash_value, h.sql_text

from v$sort_usage o, v$session s, v$sqlarea h, dba_tablespaces t

where o.session_addr = s.saddr

and o.sqladdr = h.address (+)

and o.tablespace = t.tablespace_name

order by 3 desc;

Actually, Oracle performs the sort/hash operations in PGA memory. However, if that sort operation is too large to fit into PGA memory area then it starts using temporary tablespace. If even the temporary tablespace gets all filled up then oracle user will get this error 'ORA-1652: unable to extend temp segment error'. No new sessions or queries would be possible in that case.

Thus, one of the first things you must do is to review the current value set for the PGA_AGGREGATE_TARGET initialization parameter and see if bumping it up will help.