How to check temp tablespace in Oracle

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

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;

How to check Temporary tablespace usage at 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 are 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 session using 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 resize the tempfile

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

How to drop the tempfile

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.

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

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