Temporary Tablespace Management

Find out which is the current default temporary tablespace for your database.

SELECT PROPERTY_NAME, PROPERTY_VALUE FROM DATABASE_PROPERTIES WHERE
PROPERTY_NAME='DEFAULT_TEMP_TABLESPACE';

Find out what is the current usage in temporary tablespace by using view DBA_TEMP_FREE_SPACE

SELECT * from DBA_TEMP_FREE_SPACE;

Query to find the total freespace used space, allocated in GB 

set lines 152

col FreeSpaceGB format 999.999

col UsedSpaceGB format 999.999

col TotalSpaceGB format 999.999

col host_name format a30

col tablespace_name format a30

select tablespace_name,

(free_blocks*8)/1024/1024 FreeSpaceGB,

(used_blocks*8)/1024/1024 UsedSpaceGB,

(total_blocks*8)/1024/1024 TotalSpaceGB,

i.instance_name,i.host_name

from gv$sort_segment ss,gv$instance i where ss.tablespace_name in (select tablespace_name from dba_tablespaces where contents='TEMPORARY') and

i.inst_id=ss.inst_id;

Query to help you find the total blocks, free blocks and used blocks

select inst_id, tablespace_name, total_blocks, used_blocks, free_blocks  from gv$sort_segment;

You can change the default temporary tablespace by the following command

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE <tablespace_name>;

Find out what sessions are store temporary segment in your temp tablespace

select  (select username from v$session where saddr = session_addr) uname, v.* 
from v$sort_usage v;

Creating a temporary tablespace 

CREATE TEMPORARY TABLESPACE temp_ts1
TEMPFILE 'temp_ts01.dbf' SIZE 500M AUTOEXTEND ON;

The above statement automatically extends uniformly to 1MB in size once it reaches 500MB.

CREATE TEMPORARY TABLESPACE temp_ts1
TEMPFILE 'temp_ts01.dbf' SIZE 500M
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 15M;

EXTENT MANAGEMENT LOCAL - Is an optional parameter.
UNIFORM SIZE clause qualifies for a custom extend size of 15M unlike its default  size of 1MB.