Solved

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

Posted on 2004-09-30
8
41,710 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
SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

 

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

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!

Question has a verified solution.

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

Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and theā€¦
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
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 recover a database from a user managed backup

705 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