• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 42011
  • Last Modified:

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

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
aasia
Asked:
aasia
  • 4
  • 3
1 Solution
 
schwertnerCommented:
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
 
schwertnerCommented:
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
 
aasiaAuthor Commented:
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
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
aasiaAuthor Commented:
Hi,

These issues are still open. Any comments?

Thanks
Aasia
0
 
schwertnerCommented:
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
 
aasiaAuthor Commented:
Thanks!
0
 
mcpl_at_eeCommented:
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
 
schwertnerCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now