oracledba.help

Temp Tablespaces

<- System

TOC

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

COL property_name  FORMAT a25
COL property_value FORMAT a15

-- Default Temp Tablespace for Database

SELECT property_name,property_value 
FROM database_properties 
WHERE property_name='DEFAULT_TEMP_TABLESPACE';

-- 12c (DBA_TEMP_FREE_SPACE)

COL tablespace_name FORMAT a15
SELECT tablespace_name, 
       round(tablespace_size/1024/1024/1024,0) "Size gb",
       round(allocated_space/1024/1024/1024,0) "Allocated gb",
       round(free_space/1024/1024/1024,0)      "Free gb"
FROM DBA_TEMP_FREE_SPACE;

-- Temp Tablespace Groups

SELECT group_name, tablespace_name FROM dba_tablespace_groups;

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