Solved

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

Posted on 2004-09-30
8
41,638 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
  • 4
  • 3
8 Comments
 
LVL 47

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 47

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
 

Author Comment

by:aasia
ID: 12355657
Hi,

These issues are still open. Any comments?

Thanks
Aasia
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.

 
LVL 47

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 47

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

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

Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
Via a live example, show how to take different types of Oracle backups using RMAN.

746 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