Wednesday, September 28, 2011

Script to identify who is filling up the Temporary Tablespace (ORA-1652)

You have just noticed that the TEMP tablespace is filling up fast.(see me post on finding out used and free space in TEMP tablespace at http://bash-dba.blogspot.com/2011/09/temporary-tablespace-usage.html )
Now you want to know which user and SQL statement is causing that then use the below mentioned script

SQL>select s.sid || ',' || s.serial# sid_serial, s.username, 
         o.blocks * t.block_size / 1024 / 1024 mb_used, o.tablespace, 
         o.sqladdr address, h.hash_value, h.sql_text 
         from v$sort_usage o, v$session s, v$sqlarea h, dba_tablespaces t 
         where o.session_addr = s.saddr 
         and o.sqladdr = h.address (+) 
         and o.tablespace = t.tablespace_name 
         order by s.sid;

Actually, oracle performs the sort/hash operations in PGA memory. However, if that sort opertion is too larg to fit into PGA memory area then it starts using temporary tablespace.
If even the temporary tablespace gets all filled up then oracle user will get this error

ORA-1652: unable to extend temp segment error

No new sessions or queries would be possible in that case.Thus, one of the first 
things you must do is to review the current value set for the PGA_AGGREGATE_TARGET initialization 
parameter and see if bumping it up will help. Also add the datafile in the tablespace as below

SQL>alter tablespace tablespace_name add datafile '/u01/app/oracle/data/ts01.dbf' 
         size 1000m;

No comments:

Post a Comment