Oracle TEMP tablespace usageOracle TEMP tablespace usage
Sometimes your Oracle SQL hits error ORA-1652: UNABLE TO EXTEND TEMP SEGMENT BY 256 IN TABLESPACE TEMP, this is due your SQL is running with group by, order by, nested select joining which requires TEMP tablespace for this sorting purposes. The query below can help in checking the TEMP tablespace used by each session in your Oracle database. The block size is usually 4K, 8K, 16K which you have to check before using the query below.
Query to check TEMP tablespace usage
SELECT s.username "USER",s.sid,s.osuser, u.tablespace "TS", u.blocks*&block_size/1024 "Used Space in MB" FROM v$session s,v$sort_usage u,v$sqltext x WHERE s.saddr=u.session_addr AND s.sql_address=x.address GROUP BY s.sid, s.username, osuser, tablespace, u.blocks*&block_size/1024
--Check what is the query running using the TEMP tablespace SELECT s.username "USER",s.sid,s.osuser, u.tablespace "TS", u.blocks*8/1024 "Used Space in MB" FROM v$session s,v$sort_usage u,v$sqltext x WHERE s.saddr=u.session_addr AND s.sql_address=x.address AND s.sid = '542' GROUP BY s.sid, s.username, osuser, tablespace, u.blocks*8/1024
NEXT POST » Security Scan For Weak Directory Permission
Leave a Comment
Recent Comments