Oracle-L: RE: ORA-1652: unable to extend temp segment

From: Reddy, Madhusudana <Madhusudana.Reddy_at_bestbuy.com>
Date: Mon, 25 Mar 2002 14:04:42 -0800
Message-ID: <F001.00432A1A.20020325140442@fatcity.com>

http://metalink.oracle.com/metalink/plsql/ml2_documents.showDocument?p_datab ase_id=FOR&p_id=266638.999

The above link has some comments , but Oracle folks says ALTERING THE DATA FILE will be OK. May be you can have some more inputs ...

Thanks,
Madhu

-----Original Message-----
Sent: Monday, March 25, 2002 3:30 PM
To: Multiple recipients of list ORACLE-L sp

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

--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author: Reddy, Madhusudana
INET: Madhusudana.Reddy_at_bestbuy.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------

RE: ORA-1652: unable to extend temp segment - BUT I have plenty