Glenn, Use following query to check usage of temp(temporary) tablespace and put into your log table:
select user,segtype,extents from v$sort_usage Are you having any table/indexes with degree > 1 (check dba_tables/dba_indexes) Some of your job using such tables in parallel because of degree > 1 and resulting in segtype as hash instead of sort and in this case it uses a lot of temp tablespace execeeding its total limit. There is also a possiblity that there may be some cartisian product/joining which also consumes a lot of temp tablespace and 5G is nothing when it happens... Once that job is terminated the usage goes to Zero so you cannot track it. Besides user also don't complaint because of fear of running wrong code. If nobody complaints then don't bother... HTH, Regards Rafiq Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> Date: Mon, 25 Mar 2002 13:30:26 -0800 We are periodically getting the; "ORA-1652: unable to extend temp segment by 128 in tablespace TEMP" errors. I could not identify the problem, so I set up a monitor script which would insert into a log table space usage records every 30 seconds, so I could see the space usage at the time of the failure. After we got another ORA-1652, I looked up the time and queried my log table, which showed hardly any usage in the TEMP tablespace. My question is; Why do I keep getting this error when I have plenty of free space in TEMP??? Why is it trying to extend a 128 extent when I have uniform extents (locally managed temporary tablespace and the extent sizes are 1M)? Here are my supporting settings; Temporary tablespace settings: create temporary tablespace TEMP tempfile '/RPT/oradata04/prddata/temp01.dbf' size 5000M REUSE extent management LOCAL UNIFORM size 1048576; Query at the time of the failure: select sysdate dtstamp, s.tablespace_name, d.tbspc_mb, s.total_blocks*8192/1024/1024 temp_tot_mb, s.used_blocks*8192/1024/1024 temp_used_mb, s.free_blocks*8192/1024/1024 temp_free_mb from v$sort_segment s, (select tablespace_name,sum(bytes/1024/1024) tbspc_mb from dba_data_files group by tablespace_name union select tablespace_name,sum(bytes/1024/1024) tbspc_mb from dba_temp_files group by tablespace_name) d where s.tablespace_name=d.tablespace_name; Output: Tablespace Tablespace Allocated Allocated Allocated Name Total MB Total MB Used MB Free MB -------------------- ---------- --------- --------- --------- TEMP 5,000 568 6 562 Users using temp space query; select s.sid || ',' || s.serial# sid, s.username, u.tablespace, a.sql_text, round(((u.blocks*p.value)/1024/1024),2) size_mb from v$sort_usage u, v$session s, v$sqlarea a, v$parameter p where s.saddr = u.session_addr and a.address (+) = s.sql_address and a.hash_value (+) = s.sql_hash_value and p.name = 'db_block_size' and s.username != 'SYSTEM' group by s.sid || ',' || s.serial#, s.username, a.sql_text, u.tablespace, round(((u.blocks*p.value)/1024/1024),2); Output: Temporary Mbytes Session ID User Name TS Name SQL Used ---------- ---------- ---------- ------------------------------------------------------------ --------------- 152,6214 APPS TEMP select parameter, value from nls_session_parameters 1.00 32,11293 APPS TEMP select parameter, value from nls_session_parameters 1.00 (a couple of others totalling 6MB) -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Glenn Travis INET: [EMAIL PROTECTED] Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). MOHAMMAD RAFIQ _________________________________________________________________ Get your FREE download of MSN Explorer at http://explorer.msn.com/intl.asp. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mohammad Rafiq INET: [EMAIL PROTECTED] Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).