Logo Background

Oracle TEMP Tablespace Usage

  • By on August 22, 2008 | No Comments

    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
    Previous
    Next
    » Security Scan For Weak Directory Permission
Leave a Comment