Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

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

Posted on 2004-09-30
8
Medium Priority
?
41,791 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
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 

Author Comment

by:aasia
ID: 12355657
Hi,

These issues are still open. Any comments?

Thanks
Aasia
0
 
LVL 48

Accepted Solution

by:
schwertner earned 600 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

Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
This video shows how to recover a database from a user managed backup
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

618 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