System

Temp Tablespaces

Create
Default
Display
Drop
Move
Users (Set Tablespace To)

OMF Usage

Temp Tablespace Fun Facts

Per Don Burleson, "It is completely normal for the TEMP tablespace to show at 100% full, and this does not mean that it is out of space. If you truly run out of space in the TEMP tablespace you will receive the ORA-01652 unable to extend temp segment error message":
- Oracle does not incur the overhead of scrubbing the TEMP tablespace after each use, and only marks the space as being eligible for reuse.
- Use v$tempseg_usage to see the real temporary space allocated.
In RAC environments there can be issues with contention if you have AUTOEXTEND enabled on TEMP tablespaces. So always create them with AUTOEXTEND OFF and size them adequately.

Create

-- Standard

CREATE BIGFILE TEMPORARY TABLESPACE temp1
TEMPFILE 'C:\oradata\temp1.dbf'
SIZE 2G AUTOEXTEND ON NEXT 50M MAXSIZE UNLIMITED
EXTENT MANAGEMENT LOCAL;

-- Group

CREATE BIGFILE TEMPORARY TABLESPACE temp1
TEMPFILE 'C:\oradata\temp1.dbf'
SIZE 2G AUTOEXTEND ON NEXT 50M MAXSIZE UNLIMITED
EXTENT MANAGEMENT LOCAL
TABLESPACE GROUP TEMP_TS_GROUP;

CREATE BIGFILE TEMPORARY TABLESPACE temp2
TEMPFILE 'C:\oradata\temp2.dbf'
SIZE 2G AUTOEXTEND ON NEXT 50M MAXSIZE UNLIMITED
EXTENT MANAGEMENT LOCAL;

CREATE BIGFILE TEMPORARY TABLESPACE temp3
TEMPFILE 'C:\oradata\temp3.dbf'
SIZE 2G AUTOEXTEND ON NEXT 50M MAXSIZE UNLIMITED
EXTENT MANAGEMENT LOCAL;

ALTER TABLESPACE temp2 TABLESPACE GROUP TEMP_TS_GROUP;
ALTER TABLESPACE temp3 TABLESPACE GROUP TEMP_TS_GROUP;

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP_TS_GROUP;

Database Default (set)

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE <Temp_TS_Name|Group_Name>;

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp1;

Display

-- Default Temp Tablespace for Database

COL property_name  FORMAT a25
COL property_value FORMAT a15
SELECT property_name,property_value 
FROM database_properties 
WHERE property_name='DEFAULT_TEMP_TABLESPACE';

-- Usage by User

COL osuser     FORMAT a15
COL sql_text   FORMAT a50
COL tablespace FORMAT a15
COL username   FORMAT a15
SELECT a.username, a.SID, a.serial#, a.osuser, b.tablespace, 
       b.blocks,c.sql_text
FROM v$session a, v$tempseg_usage b, v$sqlarea c
WHERE a.saddr = b.session_addr
AND c.address = a.sql_address
AND c.hash_value = a.sql_hash_value
ORDER BY b.TABLESPACE, b.blocks;

For RAC use gv$tempseg_usage.

-- Usage

set linesize 240
col name for a12
SELECT   d.status "Status", d.tablespace_name "Name", 
         d.contents "Type", 
         d.extent_management "ExtMgmnt",
         TO_CHAR(NVL(a.bytes / 1024/1024/1024, 0), '99,999,990')   "Size (gb)", 
         TO_CHAR(NVL(t.bytes,0)/1024/1024 / 1024,  '99999,999') "Used (gb)",
         TO_CHAR(NVL(t.bytes / a.bytes * 100, 0),  '990.00') "Used %"
FROM sys.dba_tablespaces d, 
     (select tablespace_name, sum(bytes) bytes from dba_temp_files group by tablespace_name) a,
     (select tablespace_name, sum(bytes_cached) bytes from v$temp_extent_pool group by tablespace_name) t
WHERE d.tablespace_name = a.tablespace_name(+) 
AND   d.tablespace_name = t.tablespace_name(+)
AND   d.extent_management like 'LOCAL' 
AND   d.contents like 'TEMPORARY';

-- RAC Instance Usage

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

-- Temp Tablespace Groups

SELECT group_name, tablespace_name FROM dba_tablespace_groups;

-- Autoextensible?

SELECT tablespace_name, autoextensible FROM dba_temp_files;

Drop Tablespace From Group

ALTER TABLESPACE temp4 TABLESPACE GROUP '';
DROP TABLESPACE temp4 INCLUDING CONTENTS AND DATAFILES;

Note: This TS cannot be taken offline first.

User Accounts

Use this to set an existing users's TEMP TS:

ALTER USER &v_schema TEMPORARY TABLESPACE &v_TmpTS;

Move

A temp tablespace (datafile) is moved by creating a new one and reasigning users to use it.

Move Session Example

CREATE BIGFILE TEMPORARY TABLESPACE temp1
TEMPFILE '/u03/data/DNARPT/datafile/temp1.dbf'
SIZE 2G AUTOEXTEND ON NEXT 50M MAXSIZE 300g
EXTENT MANAGEMENT LOCAL;

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp1; 
ALTER USER scott TEMPORARY TABLESPACE temp1;

ALTER TABLESPACE temp1 RESIZE 25g;
...
ALTER TABLESPACE temp1 RESIZE 256g;

<- System