Who is using TEMP, identify and handle

1. Identify who is using TEMP on Oracle database:

SELECT b.tablespace, ROUND(((b.blocks*p.value)/1024/1024),2)||’M’ “SIZE”, a.sid||’,’||a.serial# SID_SERIAL,a.sql_id,a.prev_sql_id,a.machine,a.osuser, a.username, a.program 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;

2. To monitor temp usage on the database(This query do not include AUTO EXTENDED size):

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;

Related Posts

About The Author

Add Comment