Temp usage

We can get information of temporary tablespace usage details with the help of below query:


Detailed Usage


Set lines 1000
set pages 300
col TABLESPACE for a20
col SIZE for a20
col SID_SERIAL for a18
col USERNAME for a20
SELECT b.tablespace,
ROUND(((b.blocks*p.value)/1024/1024),2)||'M' "SIZE",
a.sid||','||a.serial# SID_SERIAL,
a.username,
a.program,
a.CLIENT_IDENTIFIER
FROM sys.v_$session a,
sys.v_$sort_usage b,
sys.v_$parameter p
WHERE p.name = 'db_block_size'
AND a.saddr = b.session_addr
ORDER BY b.tablespace, b.blocks;

Detailed Usage ABOVE 100MB:


Set lines 1000
set pages 300
col TABLESPACE for a20
col SIZE for a20
col SID_SERIAL for a18
col USERNAME for a20
SELECT b.tablespace,
ROUND(((b.blocks*p.value)/1024/1024),2)||' MB' "SIZE",
a.sid||','||a.serial# SID_SERIAL,
a.username,
a.program,
a.CLIENT_IDENTIFIER
FROM sys.v_$session a,
sys.v_$sort_usage b,
sys.v_$parameter p
WHERE p.name = 'db_block_size'
AND a.saddr = b.session_addr and b.blocks*p.value/1024/1024>='100'
ORDER BY b.tablespace, b.blocks;

To Check the TEMP tablespace :


SELECT ROUND((SELECT SUM (tf.BYTES)
FROM dba_temp_files tf)/1024/1024/1024,2) AS "Total (GB)",
ROUND((SELECT (SUM (tu.blocks))
FROM v$tempseg_usage tu)
* (SELECT VALUE
FROM v$parameter
WHERE NAME = 'db_block_size')
/1024/1024/1024,2) AS "Used (GB)",
ROUND(((select sum(TABLESPACE_SIZE) from DBA_TEMP_FREE_SPACE) - ((SELECT (SUM (tu.blocks))
FROM v$tempseg_usage tu)
* (SELECT VALUE
FROM v$parameter
WHERE NAME = 'db_block_size')))/1024/1024/1024,2) AS "Free (GB)",
ROUND ((SELECT (SUM (tu.blocks))
FROM v$tempseg_usage tu) / (SELECT SUM (tf.blocks)
FROM dba_temp_files tf) * 100,2) AS "Used_pct(%)" FROM DUAL;