Solved

ORA-01652: unable to extend temp segment by 128 in tablespace TEMP

Posted on 2004-08-27
9
10,503 Views
Last Modified: 2011-08-18
Hi.

I have Oracle 9.2.0.1 running on Compaq Tru64 UNIX V5.1

I have run into a problem with the temp tablespace.

I first noticed it when some of my webpages that are served off the database stopped working giving the following error message:

ORA-01652: unable to extend temp segment by 128 in tablespace TEMP

I opened up the folder where the file lives and it appeared as though the drive was full.  The TEMP01.dbf file was around 11GB!!.  I made some room by removing some other files but it still didnt work.  I then went into enterprise manager and the temp tablespace is showing up as 0 MB.

This is an online system so I cant test a solution... I need a solution that works and please dont hesitate to ask any questions.

Asim

P.S. I suspect the TEMP file blew out because of a new procedure that was being tested.   I just need to get it back up and running.
0
Comment
Question by:asiminator
  • 3
  • 3
  • 2
  • +1
9 Comments
 
LVL 13

Assisted Solution

by:riazpk
riazpk earned 75 total points
Comment Utility
Hopefully you will be using LMT Temp....

You are perhaps doing tons of Group by, Order By in your database.
From:
http://asktom.oracle.com/pls/ask/f?p=4950:8:15982440970043111116::NO::F4950_P8_DISPLAYID,F4950_P8_CRITERIA:374218170986,

Well, in 817, we can use a SEVERERROR trigger to capture information when this
happens.  This one captures all of the SQL cursors the current session that hits
this problem has open:

ops$tkyte@ORA817DEV.US.ORACLE.COM> create table t ( msg varchar2(4000) );

Table created.

ops$tkyte@ORA817DEV.US.ORACLE.COM>
ops$tkyte@ORA817DEV.US.ORACLE.COM> create or replace trigger
failed_to_extend_temp
  2  after servererror on database
  3  declare
  4      
  5      
  6  begin
  7      if ( is_servererror(1652) )
  8      then
  9          insert into t values ( 'ora_sysevent = ' || ora_sysevent );
 10          insert into t values ( 'ora_login_user = ' || ora_login_user );
 11          insert into t values ( 'ora_server_error = ' || ora_server_error(1)
);
 12
 13          insert into t select 'open cursor ' || rownum || ' ' || sql_text
 14                          from v$open_cursor where sid = (select sid from
v$mystat where rownum=1);
 15      end if;
 16  end;
 17  /

Trigger created.

ops$tkyte@ORA817DEV.US.ORACLE.COM>
ops$tkyte@ORA817DEV.US.ORACLE.COM> create temporary tablespace test_temp
  2  tempfile '/tmp/test_temp.dbf' size 512k reuse
  3  extent management local
  4  uniform size 64k
  5  /

Tablespace created.

ops$tkyte@ORA817DEV.US.ORACLE.COM>
ops$tkyte@ORA817DEV.US.ORACLE.COM> alter user ops$tkyte temporary tablespace
test_temp
  2  /

User altered.

ops$tkyte@ORA817DEV.US.ORACLE.COM>
ops$tkyte@ORA817DEV.US.ORACLE.COM> select * from all_objects
  2  order by 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12;
select * from all_objects
              *
ERROR at line 1:
ORA-01652: unable to extend temp segment by 8 in tablespace TEST_TEMP


ops$tkyte@ORA817DEV.US.ORACLE.COM>
ops$tkyte@ORA817DEV.US.ORACLE.COM> select * from t;

MSG
---------------------------------------------------------------------------------
--------------------------------------------------
ora_sysevent = SERVERERROR
ora_login_user = OPS$TKYTE
ora_server_error = 1652
open cursor 1 INSERT INTO T SELECT 'open cursor ' || ROWNUM  || ' '  || SQ
open cursor 2 insert into sys.aud$( sessionid,entryid,statement,timestamp#
open cursor 3 declare     l_sql_text ora_name_list_t;     l_n        numbe
open cursor 4 INSERT INTO T VALUES ( 'ora_login_user = ' || ORA_LOGIN_USER
open cursor 5 INSERT INTO T VALUES ( 'ora_server_error = ' || ORA_SERVER_E
open cursor 6 select * from all_objects order by 1, 2, 3, 4, 5, 6, 7, 8, 9
open cursor 7 INSERT INTO T VALUES ( 'ora_sysevent = ' || ORA_SYSEVENT   )

10 rows selected.

ops$tkyte@ORA817DEV.US.ORACLE.COM>



you'll be able to narrow it down from there (hopefully)..



In 9i -- there is a new function that will pinpoint this exactly:

ops$tkyte@ORA920> create or replace trigger failed_to_extend_temp
  2  after servererror on database
  3  declare
  4      l_sql_text ora_name_list_t;
  5      l_n        number;
  6  begin
  7      if ( is_servererror(1652) )
  8      then
  9          insert into t values ( 'ora_sysevent = ' || ora_sysevent );
 10          insert into t values ( 'ora_login_user = ' || ora_login_user );
 11          insert into t values ( 'ora_server_error = ' || ora_server_error(1)
);
 12
 13                  l_n := ora_sql_txt( l_sql_text );
 14                  for i in 1 .. l_n
 15                  loop
 16                          insert into t values ( 'l_sql_text(' || i || ') = '
|| l_sql_text(i) );
 17                  end loop;
 18      end if;
 19  end;
 20  /

Trigger created.

ops$tkyte@ORA920>
ops$tkyte@ORA920> create temporary tablespace test_temp
  2  tempfile '/tmp/test_temp.dbf' size 512k reuse
  3  extent management local
  4  uniform size 64k
  5  /

Tablespace created.

ops$tkyte@ORA920>
ops$tkyte@ORA920> alter user ops$tkyte temporary tablespace test_temp
  2  /

User altered.

ops$tkyte@ORA920>
ops$tkyte@ORA920> select * from all_objects
  2  order by 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12;
select * from all_objects
              *
ERROR at line 1:
ORA-01652: unable to extend temp segment by 8 in tablespace TEST_TEMP


ops$tkyte@ORA920>
ops$tkyte@ORA920> select * from t;

MSG
---------------------------------------------------------------------------------
--------------------------------------------------
ora_sysevent = SERVERERROR
ora_login_user = OPS$TKYTE
ora_server_error = 1652
l_sql_text(1) = select * from all_objects
order by 1, 2, 3, 4, 5, 6, 7, 8, 9, 10

l_sql_text(2) = , 11, 12

ops$tkyte@ORA920>
0
 

Author Comment

by:asiminator
Comment Utility
Hi.

Thanks for that... but my main problem at the moment is fixing my temp tablespace problem.  I will use this once I get the tablespace fixed.

The file being 11GB and enterprise manager saying that it is 0MB is a bigger concern.

Thanks

Asim
0
 
LVL 23

Expert Comment

by:seazodiac
Comment Utility
just log in enterprise manager or sqlplus as sysdba
execute the following to add free space to temp tablespace:


SQL> alter tablespace temp add tempfile '<Path to a filename like temp01.dbf>' size 300M;


then you are good to go for now.
0
 
LVL 13

Assisted Solution

by:riazpk
riazpk earned 75 total points
Comment Utility
-- This SQL Plus script lists freespace by tablespace
--------------------------------------------------------

column dummy noprint
column  pct_used format 999.9       heading "%|Used"
column  name1    format a16      heading "Tablespace Name"
column  Kbytes   format 999,999,999    heading "KBytes"
column  used    format 999,999,999   heading "Used"
column  free    format 999,999,999  heading "Free"
column  largest    format 999,999,999  heading "Largest"
break   on report
compute sum of kbytes on report
compute sum of free on report
compute sum of used on report

select nvl(b.tablespace_name,
             nvl(a.tablespace_name,'UNKOWN')) name1,
       kbytes_alloc kbytes,
       kbytes_alloc-nvl(kbytes_free,0) used,
       nvl(kbytes_free,0) free,
       ((kbytes_alloc-nvl(kbytes_free,0))/
                          kbytes_alloc)*100 pct_used,
       nvl(largest,0) largest
from ( select sum(bytes)/1024 Kbytes_free,
              max(bytes)/1024 largest,
              tablespace_name
       from  sys.dba_free_space
       group by tablespace_name ) a,
     ( select sum(bytes)/1024 Kbytes_alloc,
              tablespace_name
       from sys.dba_data_files
       group by tablespace_name )b
where a.tablespace_name (+) = b.tablespace_name
order by 1
/
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Accepted Solution

by:
ramaswamymorla earned 50 total points
Comment Utility
We have 2 separate bugs here:
bug#2934117 ORA-1652:CAN'T ACQUIRE FREE EXTENTS OWNED BY OTHER NODE
for when scripts fail and also
bug#2858082 EVEN IF THE FREE EXTENTS ARE AVAILABLE, ORA-1652 OCCURS IN ALERT.
LOG ON RAC
when scripts do not fail but errors appear inside alert.log of RAC instances.
Fix 1st Case (bug#2934117)
====================

Due to bug#2934117 we should NOT use a DEFAULT TEMPORARY TABLESPACE
for RAC database configuration, otherwise scripts could fail with ORA-1652 as a RAC node
cannot acquire free extents that are owned by another RAC instance although they are free.
This is solved with the following workaround:

Workaround
------------------
step 1: Create new normal temp tablespace with minimum size (for example TEMPRAC )
step 2: Set as default temp tablespace TEMPRAC
alter database default temporary tablespace TEMPRAC;
This commands changes all database users temporary tablespace to TEMPRAC
Now previous default temporary tablespace TEMP is a normal temporary tablespace.
step 3: For all database users change back temporary tablespace to TEMP:
alter user default tempory teblespace TEMP;
step 4: shutdown immediate all instances
step 5: startup all instance

Unfortunatelly this TEMPRAC Default Temporary Tablespace cannot be dropped
completely.
We should keep it with small size.
In case new Database users are created, and temporary tablespace is not defined
at creation time, these would
have as temporary tablespace TEMPRAC, so every new user after creation we have
to specify:
alter user temporary tablespace TEMP;
-> in order to change their temporary tablespace to TEMP which is a normal
temporary tablespace.

2nd case (bug#2858082)
==================
The 2nd issue is caused due to the bug#2858082 "EVEN IF THE FREE EXTENTS ARE
AVAILABLE, ORA-1652 OCCURS IN ALERT.LOG ON RAC "
In this bug although scripts that need Temp tablespace space due to Sort
Operations (order by, group by, join, etc)
are successfull we may see this error message inside alert.log of RAC instances.
When a RAC instance fills its own sort pool of extents, instead of allocation
new extents it may ask for free temp extents
from the other RAC instances. During this operation the ORA-1652 may appear
inside the alert.log although the operation has succeeded.
This bug would only be solved in subsequent releases as it has been
characterised as a low priority issue from development as only dis-affect is that alert.log
may contain such messages but process do not fail.
You can ignore these error messages.
0
 

Author Comment

by:asiminator
Comment Utility
0
 
LVL 23

Expert Comment

by:seazodiac
Comment Utility
that's exactly what I proposed,
but anyway, your problem is solved.
0
 
LVL 23

Expert Comment

by:seazodiac
Comment Utility
what the heck? I thought you are gonna to take back your points,

now that you assigned, at least you can do that right.

I am the only one in this post to give you at least the right track to do things.

and I don't get any points????
0
 

Author Comment

by:asiminator
Comment Utility
I did not use your comment at all. Hence, no points.  It seemed like a rather simplistic approach to be taking, so it was basically ignored.  The answer was actually provided by Sergio Leunissen from Oracle so the points should theoretically go to him. ramaswamymorla provided something close... thats why he got the accepted answer and the other dude put a lot of effort into his response.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Article by: Swadhin
From the Oracle SQL Reference (http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/queries006.htm) we are told that a join is a query that combines rows from two or more tables, views, or materialized views. This article provides a glimps…
This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now