How to check Oracle TEMP space usage: Easy Guide

Post published:September 17, 2024

Monitoring Oracle TEMP space usage ensures your Oracle Database runs smoothly. This post will cover everything you need to know about Oracle TEMP space usage. Let’s open VirtualBox and get started!

Here are some key methods to monitor and analyze Oracle TEMP space usage:

1. Check TEMP space per session:

COLUMN TABLESPACE FORMAT A15
COLUMN TEMP_SIZE FORMAT A10
COLUMN SID_SERIAL FORMAT A15
COLUMN USERNAME FORMAT A12
COLUMN PROGRAM FORMAT A30
SET LINESIZE 200
SELECT
x.sid||','||x.serial# AS SID_SERIAL,y.sql_id,y.segtype,
NVL(x.username, '(oracle)') AS username,
x.program, y.tablespace,ROUND(((y.blocks*p.value)/1024/1024),2)||'MB' AS TEMP_SIZE
FROM gv$session x,
gv$sort_usage y,
gv$parameter p
WHERE p.name = 'db_block_size'
AND x.saddr = y.session_addr and y.TABLESPACE='&TEMP_TABLESPACE'
ORDER BY y.tablespace, y.blocks
/

How to check Oracle TEMP space usage: Easy Guide

If you do not know what the default temp tablespace then you can run the below command:

SELECT PROPERTY_VALUE FROM DATABASE_PROPERTIES where PROPERTY_NAME='DEFAULT_TEMP_TABLESPACE';

2. Temp tablespace used by user:

COLUMN TABLESPACE FORMAT A15
COLUMN USERNAME FORMAT A12
COLUMN OSUSER FORMAT A15
SET LINESIZE 1000
SET PAGES 1000
select 
   x.tablespace, 
   x.segfile#, 
   x.segblk#, 
   x.blocks, 
   y.sid, 
   y.serial#, 
   y.username, 
   y.osuser, 
   y.status 
from 
   v$session y,
   v$sort_usage x 
where 
   y.saddr = x.session_addr 
order by 
   x.tablespace, x.segfile#, x.segblk#, 
   x.blocks;