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).