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

From: Mohammad Rafiq <rafiq9857_at_hotmail.com>
Date: Mon, 25 Mar 2002 14:13:20 -0800
Message-ID: <F001.00432A4E.20020325141320@fatcity.com>

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: ORACLE-L_at_fatcity.com
To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com> 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: 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).




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

Received on Mon Mar 25 2002 - 16:13:20 CST

Re: ORA-1652: unable to extend temp segment - BUT I have plenty of sp