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