Message-ID: <F001.004329CA.20020325133026@fatcity.com>
Date: Mon, 25 Mar 2002 13:30:26 -0800
To: Multiple recipients of list ORACLE-L <ORACLE-L@fatcity.com>
Sender: root@fatcity.com
Reply-To: ORACLE-L@fatcity.com
Errors-To: ML-ERRORS@fatcity.com
From: Glenn Travis <Glenn.Travis@sas.com>
Subject: ORA-1652: unable to extend temp segment - BUT I have plenty of sp
Organization: Fat City Network Services, San Diego, California
Precedence: bulk
Mime-Version: 1.0
Content-Type: text/plain;	charset="iso-8859-1"
Content-Transfer-Encoding: 7bit

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: Glenn.Travis@sas.com

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: ListGuru@fatcity.com (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).