oracledba.help
System

Temp Tablespaces

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 username FORMAT a15
SELECT username, sql_id, session_num, blocks*(8192)/(1024*1024) mb 
FROM v$tempseg_usage;

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;

<- System


 SELECT  TU.USERNAME,S.SID,S.SERIAL# FROM V$TEMPSEG_USAGE TU, V$SESSION S WHERE TU.SESSION_ADDR=S.SADDR;