Solved

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

Posted on 2004-09-30
8
41,691 Views
Last Modified: 2012-01-02
Hello All,

We are getiing this ORA error when we are performing a high volume task(search, sort, grouping, insertion of more than 4 million records). We are creating LMT. The current default TEMP TS size is set to 18G. We are using Oracle 9.2.0.3 on SPARC Sun Solaris OS 5.8. would appreciate an early response as this is a show stopper.

Regards
Aasia

P.S: Am assigning all the points that I have currently, sorry about it being low.
0
Comment
Question by:aasia
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
8 Comments
 
LVL 48

Expert Comment

by:schwertner
ID: 12197687
Points are not important. Keep them for future use.

ORA-01652 Temporary Segment  
===========================  
 
      SELECT  next_extent, pct_increase, tablespace_name  
      FROM    dba_tablespaces  
      WHERE   tablespace_name = '<tablespace name>';  
 
        Temporary  segments take the default storage clause of the tablespace  
        in which they are created.
 
      If this error is caused by a query, then try and ensure that the query  
      is tuned to perform its sorts as efficiently as possible.


There are several options for solving errors due to failure to extend:
 
 
   a. Manually Coalesce Adjacent Free Extents
      ---------------------------------------
 
      ALTER TABLESPACE <tablespace name> COALESCE;
 
      The extents must be adjacent to each other for this to work.
 
 
   b. Add a Datafile
      --------------
 
      ALTER TABLESPACE <tablespace name>  
      ADD DATAFILE '<full path and file name>' SIZE <integer> <k|m>;  
c. Lower "next_extent" and/or "pct_increase" size
      ----------------------------------------------
 
      For non-temporary and non-partitioned segment problem:  
 
      ALTER <segment_type> <segment_name>  
      STORAGE ( next <integer> <k|m> pctincrease <integer>);  
 
      For non-temporary and partitioned segment problem:  
 
      ALTER TABLE <table_name> MODIFY PARTITION <partition_name>
      STORAGE ( next <integer> <k|m> pctincrease <integer>);  
 
      For a temporary segment problem:  
 
      ALTER TABLESPACE <tablespace name>  
      DEFAULT STORAGE (initial <integer> next <integer> <k|m> pctincrease <integer>);  
 
 
    d. Resize the Datafile
       -------------------
 
       ALTER DATABASE DATAFILE '<full path and file name>'  
       RESIZE <integer> <k|m>;  
 
 
    e. Defragment the Tablespace
       -------------------------
This is a complex task
0
 
LVL 48

Expert Comment

by:schwertner
ID: 12197698
Of course 4 milions records is a respectable huge amount. Try to use more indexes, to preselect in a view part of these rows or to use some tricks to reduce the considered in your queries rows.
0
 

Author Comment

by:aasia
ID: 12257563
Thanks for the inputs. We have added new data files. The problem is
a) How do I do this in the production environment?
b) When will the temp space be cleared? Currently even after the process has completed, the temp space is not cleared unless we explicitly delete the temp data files.

-Aasia
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:aasia
ID: 12355657
Hi,

These issues are still open. Any comments?

Thanks
Aasia
0
 
LVL 48

Accepted Solution

by:
schwertner earned 150 total points
ID: 12356124
This is a bug in Oracle. You have to recreate the temporay tablespace.
Hi. There is a bug (bug 2193406) filed against 9.2, although it appears to effect 8i (bug 1003703) as well, which is an issue where you are unable to resize a temporary tablespace even though it is not currently being used. The bug is assigned to development. The workaround is to drop and re-add the tempfile(s).

You can monitor the v$tempseg_usage view to see what user is currently utilizing the temporary space. You may also want to turn off autoextend and/or set a maxsize.


CREATE
    TEMPORARY TABLESPACE "TEMP1" TEMPFILE
    'E:\ORACLE_DB_MAIN\ORADATA\MAIN\TEMP02.DBF' SIZE 187039K
    REUSE AUTOEXTEND
    ON NEXT  640K MAXSIZE  32767M EXTENT MANAGEMENT LOCAL UNIFORM
    SIZE 1024K;

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE "TEMP1";

DROP TABLESPACE TEMP INCLUDING CONTENTS AND DATAFILES;


CREATE
    TEMPORARY TABLESPACE "TEMP" TEMPFILE
    'E:\ORACLE_DB_MAIN\ORADATA\MAIN\TEMP01.DBF' SIZE 187039K
    REUSE AUTOEXTEND
    ON NEXT  640K MAXSIZE  32767M EXTENT MANAGEMENT LOCAL UNIFORM
    SIZE 1024K;

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE "TEMP"



drop tablespace temp1 including contents and datafiles;
0
 

Author Comment

by:aasia
ID: 12356790
Thanks!
0
 

Expert Comment

by:mcpl_at_ee
ID: 37354881
following URL having indepth details on Temp. Tablespace of Oracle ... very well documented...

http://oratips-ddf.blogspot.com/2008/02/temporary-tablespace-insanity.html
0
 
LVL 48

Expert Comment

by:schwertner
ID: 37359021
Nowadays we try first to ask SMON to free unused segments in temporary tablespace.
This is task of SMON. For some reason it doesn't work.
This is the main reason for this situation in about 90% of the cases.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
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.

733 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