How to check temp tablespace in Oracle: Size, resize, add, drop

Table of Contents

What is Temporary Tablespace

A temporary tablespace is used for sorting operations in Oracle. It is also used in the join operations. Basically, it is temporary storage and temporary objects are created here. Global temporary tables are also created in this tablespace. This is quite a significant tablespace and can pose issues if not managed correctly. Let’s see various queries for the Management of Temporary tablespace

How to create temp tablespace in Oracle

Here is the SQL to create the temp tablespace

Single tempfile
CREATE TEMPORARY TABLESPACE TEMP  TEMPFILE '/u01/oracle/TEST/oradata/temp_1.dbf' SIZE 1000M;
Multiple tempfile
CREATE TABLESPACE TEMP  TEMPFILE
'/u01/oracle/TEST/oradata/temp_1.dbf' SIZE 1000M
'/u01/oracle/TEST/oradata/temp_2.dbf' SIZE 1000M
;
With ASM
CREATE TEMPORARY TABLESPACE TEMP  TEMPFILE '+DATA' SIZE 1000M;

How to check temp tablespace in Oracle

Oracle database can have more than one temporary tablespace

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

We can select the files in each of these tablespace using the below query

select file_name, tablespace_name, bytes,autoextensible 
 from dba_temp_files
or
select a.name,b.name, a.bytes from v$tempfile a, v$tablespace b where a.ts#=b.ts#

How to check the size of the Temp tablespace

Here is the query on how to check the temp tablespace size in GB in Oracle

select tablespace_name,sum(bytes)/1024/1024/1024 temp_gb
from dba_temp_files group by tablespace_name;
or
select b.name, sum(a.bytes)/1024/1024/1024 from v$tempfile a, v$tablespace b where a.ts#=b.ts# group by b.name;

If you want to calculate in MB, then use the below query

select tablespace_name,sum(bytes)/1024/1024 temp_mb from dba_temp_files group by tablespace_name; 
or 
select b.name, sum(a.bytes)/1024/1024 from v$tempfile a, v$tablespace b where a.ts#=b.ts# group by b.name;

How to check free space in temp tablespace in Oracle

Here is the query for that

select tsh.tablespace_name,
dtf.bytes/1024/1024/1024 total_GB,
sum(nvl(tsh.bytes_used,0))/1024/1024/1024 used_GB,
sum(nvl(tsh.bytes_free,0))/1024/1024/1024 free_GB,
(1-sum(nvl(tsh.bytes_free,0))/dtf.bytes)*100 pct,
from v$temp_space_header tsh,
(select tablespace_name, sum(bytes) bytes
from dba_temp_files
group by tablespace_name) dtf
where dtf.tablespace_name = tsh.tablespace_name(+)
group by tsh.tablespace_name, dtf.bytes/1024, dtf.bytes
order by 1
/

With 11g, A new view dba_temp_free_space is introduced, so we can use that also as

SELECT * FROM dba_temp_free_space;

How to check Temporary tablespace usage at the instance level

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

select inst_id, tablespace_name, blocks_cached, blocks_used
from gv$temp_extent_pool;

select inst_id,tablespace_name, blocks_used, blocks_free
from gv$temp_space_header;

select inst_id,free_requests,freed_extents
from gv$sort_segment;

How to check the usage percentage of Temp tablespace

select tablespace_name, ' %free='|| round(sum(free_blocks) / sum(total_blocks) * 100 ,0)
from gv$sort_segment group by tablespace_name;

How to find the session which is using temporary tablespace

set pagesize 50000 echo off verify off feedback off lines 80 heading on
col username format a15 head 'Username'
col osuser format a15 head 'OS User'
col sid format 99999 head 'Sid'
col logon_time format a14 head 'Logon Time'
col tablespace format a15 head 'Tablespace Name'
select
tablespace
, a.osuser
, a.username
, a.sid
, to_char(logon_time,'MM/DD/YY HH24:MI') logon_time
from v$session a, v$sort_usage b
where a.saddr = b.session_addr;

or

SET ECHO OFF
SET FEEDBACK 6
SET HEADING ON
SET LINESIZE 180
SET PAGESIZE 50000
SET TERMOUT ON
SET TIMING OFF
SET TRIMOUT ON
SET TRIMSPOOL ON
SET VERIFY OFF

CLEAR COLUMNS
CLEAR BREAKS
CLEAR COMPUTES

COLUMN instance_name FORMAT a9 HEADING ‘Instance’
COLUMN tablespace_name FORMAT a15 HEADING ‘Tablespace Name’
COLUMN sid FORMAT 99999 HEADING ‘SID’
COLUMN serial_id FORMAT 99999999 HEADING ‘Serial ID’
COLUMN session_status FORMAT a9 HEADING ‘Status’
COLUMN oracle_username FORMAT a18 HEADING ‘Oracle User’
COLUMN os_username FORMAT a18 HEADING ‘O/S User’
COLUMN os_pid FORMAT a8 HEADING ‘O/S PID’
COLUMN session_terminal FORMAT a10 HEADING ‘Terminal’ TRUNC
COLUMN session_machine FORMAT a30 HEADING ‘Machine’ TRUNC
COLUMN session_program FORMAT a20 HEADING ‘Session Program’ TRUNC
COLUMN contents FORMAT a9 HEADING ‘Contents’
COLUMN extents FORMAT 999,999,999 HEADING ‘Extents’
COLUMN blocks FORMAT 999,999,999 HEADING ‘Blocks’
COLUMN bytes FORMAT 999,999,999,999 HEADING ‘Bytes’
COLUMN segtype FORMAT a12 HEADING ‘Segment Type’

BREAK ON instance_name SKIP PAGE

SELECT
i.instance_name instance_name
, t.tablespace tablespace_name
, s.sid sid
, s.serial# serial_id
, s.status session_status
, s.username oracle_username
, s.osuser os_username
, p.spid os_pid
, s.program session_program
, t.contents contents
, t.segtype segtype
, (t.blocks * c.value) bytes
FROM
gv$instance i
, gv$session s
, gv$process p
, gv$sort_usage t
, (select value from v$parameter
where name = ‘db_block_size’) c
WHERE
s.inst_id = p.inst_id
AND p.inst_id = i.inst_id
AND t.inst_id = i.inst_id
AND s.inst_id = i.inst_id
AND s.saddr = t.session_addr
AND s.paddr = p.addr
ORDER BY
i.instance_name
, s.sid;

How to check the temp usage by Session

SELECT s.sid, s.username, s.status, u.tablespace, u.segfile#, u.contents, u.extents, u.blocks
FROM v$session s, v$sort_usage u
WHERE s.saddr=u.session_addr
ORDER BY u.tablespace, u.segfile#, u.segblk#, u.blocks;

If we want to see the sql being run also, then

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;

How to find sessions using a lot of TEMP

col inst_id format 999
col sid format 99999
col status format a11
col program format a20 trunc
col machine format a17 trunc
col action format a39
col module format a39
col blocks heading "TEMP BLOCKS"
SELECT b.blocks, s.inst_id, s.sid, s.status,
s.action,
s.program,
s.machine,
s.module
FROM gv$session s,
gv$sort_usage b
WHERE s.saddr = b.session_addr
and s.inst_id = b.inst_id
and b.blocks > 100000
order by b.blocks desc;

How to increase temp tablespace in Oracle

We can increase the temp tablespace by either extending the existing file or we can add the new temp file.

alter database tempfile '+DATA/test/tempfile01.dbf' resize 10000m ;
or
alter tablespace TEMP add tempfile '+DATA/test/tempfile02.dbf' resize 10000m ;

How to resize the tempfile

alter database tempfile '+DATA/test/tempfile01.dbf' resize 10000m ;

How to drop the tempfile

It is not possible to drop the datafile from the Normal Tablespace but it is possible to drop the tempfile from the temporary tablespace. Here is the sql

ALTER DATABASE TEMPFILE '<temp file>' DROP INCLUDING DATAFILES;

As for the users who were using the temp file, their transaction will end and will be greeted with the following error message:

ERROR at line 4:
ORA-00372: file 601 cannot be modified at this time
ORA-01110: data file 601: '<temp file>'
ORA-00372: file 601 cannot be modified at this time
ORA-01110: data file 601: '<temp file>'

If this happens, you should attempt to drop the temp file again so the operation is successful:

SQL> ALTER DATABASE TEMPFILE '<temp file>' DROP INCLUDING DATAFILES;
Database altered.

How to drop temp tablespace in oracle

We can drop the temp tablespace using the below command

drop tablespace including contents and datafiles;

Important View for temp information

How to change the default temporary tablespace of the user

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

How to change the default Temp tablespace of the Database

SQL> SELECT property_name,property_value FROM DATABASE_PROPERTIES where PROPERTY_NAME='DEFAULT_TEMP_TABLESPACE';
PROPERTY_NAME PROPERTY_VALUE
------------- ---------------
DEFAULT_TEMP_TABLESPACE TEMP

alter database default temporary tablespace temp2;

SQL> SELECT property_name,property_value FROM DATABASE_PROPERTIES where PROPERTY_NAME='DEFAULT_TEMP_TABLESPACE';
PROPERTY_NAME PROPERTY_VALUE
------------ -------------
DEFAULT_TEMP_TABLESPACE TEMP2

I hope you like queries on How to check temp tablespace in Oracle, How to create temp tablespace in Oracle,How to drop temp tablespace in Oracle,how to check free space in temp tablespace in Oracle

Also Reads
Oracle Create Tablespace: This article on how to create tablespace in oracle, various characteristics associated with it and different create tablespace statements
ORA-01652:ORA-01652 error usually because when the tablespace does not have free space in Permanent and Temporary tablespace in oracle database. Check out how to resolve it
How to check Tablespace in Oracle: Learn about How to Check Tablespace in Oracle, tablespace free space,tablespace growth information,tablespace size ,associated datafiles ,tablespace size
Automatic Workload Repository: Learn about Automatic Workload Repository(AWR).About views, table, how to purge information, how to collect and frequency of collection
Oracle database administration tutorial: This lists all the Oracle DBA-related stuff. Very helpful for administration
how to move lob segment: how to move lob segment from one tablespace to another, how to reclaim space after major deleted in lob segment
how to get table definition in oracle: Check out how to get table definition in oracle, oracle show index definition, get ddl of a materialized view in oracle, get the query of a view in oracle
https://docs.oracle.com/cd/B10501_01/server.920/a96540/statements_75a.htm