Check usage of TEMP tablespace with non CDB and PDBs database

Check usage of TEMP tablespace with non CDB and PDBs database

Check the Temp Tablespace Usages

SELECT A.tablespace_name tablespace, D.mb_total,
SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_used,
D.mb_total - SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_free
FROM v$sort_segment A,
(
SELECT B.name, C.block_size, SUM (C.bytes) / 1024 / 1024 mb_total
FROM v$tablespace B, v$tempfile C
WHERE B.ts#= C.ts#
GROUP BY B.name, C.block_size) D
WHERE A.tablespace_name = D.name
GROUP by A.tablespace_name, D.mb_total;

TABLESPACE           MB_TOTAL    MB_USED    MB_FREE
------------------- --------- ---------- ----------
TEMP                     2220          0       2220


Check Temp tablespace usages for all pdbs which are in open state

col tablespace for a10
SELECT d.con_id,A.tablespace_name tablespace, D.mb_total,
SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_used,
D.mb_total - SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_free
FROM v$sort_segment A,
(
SELECT b.con_id, b.tablespace_name, C.block_size, SUM (C.bytes) / 1024 / 1024 mb_total
FROM cdb_temp_files B, v$tempfile C
WHERE B.con_id= C.con_id and b.file_name = c.name
GROUP BY b.con_id,b.tablespace_name, C.block_size) D
WHERE A.tablespace_name = D.tablespace_name
GROUP by D.con_id,A.tablespace_name, D.mb_total;

     CON_ID TABLESPACE   MB_TOTAL    MB_USED    MB_FREE
---------- ---------- ---------- ---------- ----------
         1 TEMP              129          0        129
         4 TEMP               62          0         62
         3 TEMP              129          0        129
         5 TEMP               62          0         62


Check Input/Output usage of each temp file give you all PDBs or non container temp files

set line 200 pages 200
col file_name for a40
SELECT SUBSTR(t.name,1,50) AS file_name,
f.phyblkrd AS blocks_read,
f.phyblkwrt AS blocks_written,
f.phyblkrd + f.phyblkwrt AS total_io
FROM v$tempstat f,v$tempfile t
WHERE t.file# = f.file#
ORDER BY f.phyblkrd + f.phyblkwrt DESC;

Session wise usage of Temp file

select * from (SELECT u.tablespace, s.username, s.sid, s.serial#, s.logon_time, program, u.extents, ((u.blocks*8)/1024) as MB,
i.inst_id,i.host_name
FROM gv$session s, gv$sort_usage u ,gv$instance i
WHERE s.saddr=u.session_addr and u.inst_id=i.inst_id order by MB DESC) a where rownum<10;

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.