How to check temp tablespace in Oracle

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 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 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;

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 user

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

How to change the default Temp tablespace of 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
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