Akdora’s Blog

Programming, Oracle, Life, Fun

Ora-01652 : Unable to extend temp segment by 128 in tablespace January 24, 2008

That error occurs when failed to allocate an extent of the required number of blocks for a temporary segment in the tablespace indicated.
So we can this in two ways.

 

1.
Check the datafiles sizes..

SELECT * FROM DBA_DATA_FILES;
     FILE_NAME                 FILE_ID  TABLESPACE_NAME     BYTES    BLOCKS    STATUS
1 D:\ORACLEXE\ORADATA\XE\USERS.DBF    4       USERS          104857600    12800   AVAILABLE
2 D:\ORACLEXE\ORADATA\XE\SYSAUX.DBF   3       SYSAUX         450887680    55040   AVAILABLE
3 D:\ORACLEXE\ORADATA\XE\UNDO.DBF     2       UNDO            94371840    11520   AVAILABLE
4 D:\ORACLEXE\ORADATA\XE\SYSTEM.DBF   1       SYSTEM         629145600    76800   AVAILABLE

Then resize your datafile or add a new datafile to current tablespace
Resizing Datafile :
ALTER DATABASE DATAFILE ‘D:\ORACLEXE\ORADATA\XE\USERS.DBF‘ RESIZE 200M;
Addind Datafile to existing Tablespace:
ALTER TABLESPACE USERS ADD DATAFILE ‘D:\ORACLEXE\ORADATA\XE\USERS_2.DBF‘ SIZE 50M;

 

2.
Change the user’s default tablespace to a bigger one :)

SELECT * FROM Dba_Users;
  USERNAME USER_ID ACCOUNT_STATUS  DEFAULT_TABLESPACE
1 ASD         36     OPEN              SYSTEM
2 SYS          0     OPEN              SYSTEM
3 SYSTEM       5     OPEN              SYSTEM
4 ANONYMOUS   28     OPEN              SYSAUX   
ALTER USER asd DEFAULT TABLESPACE users;

It changes “asd” schema’s default tablespace to USERS tablespace.

SELECT * FROM Dba_Users;
USERNAME USER_ID  ACCOUNT_STATUS  DEFAULT_TABLESPACE
1 ASD       36        OPEN             USERS
2 SYS        0        OPEN             SYSTEM
3 SYSTEM     5        OPEN             SYSTEM
4 ANONYMOUS 28        OPEN             SYSAUX  

You can also do this per object :  “alter table table_name move tablespace users; “

You can also select the tablespace in the create script of the object.

 

 

References :
http://www.psoug.org/reference/tablespaces.html
http://www.psoug.org/reference/datafiles.html

About these ads
 

11 Responses to “Ora-01652 : Unable to extend temp segment by 128 in tablespace”

  1. what about database control? ;)

  2. Kalidhas Says:

    Hi,

    This is great! I resolved one of Oracle issues with this. Keep doing the good work.

    If you can find a doc on the files and table involved in Oracle authentication( Schema Oriented) it would be more help full for me and others…

    Thanks,
    Kalidhas K Arumugam

  3. I guess you need SYSDBA privileges in order to execute these statements. My project has just been set back two weeks!

  4. celebs Says:

    Thanks great solution!

  5. senthil Says:

    so helpfull

  6. Manoj Says:

    thanx.. solved my problem in a sec..

    Bookmarked.

    Manoj

  7. Thirumalai Says:

    Thanks, a lot

  8. Muthukumar Says:

    Hello,

    These’re the regular solutions offered whenever we hit this error. But, what if the same error occurs in the following scenario ?

    - User X has a default tablespace X_DEF and temporary tablespace TEMP
    - TEMP tablespace has more free space
    - A program run by X is giving the error “unable to extend temp segment by 8 in tablespace X_DEF”

    Why should this program try to extend a temp segment in the default tablespace itself instead of TEMP tablespace, which has been assigned it’s temporary tablespace ?

    Thanks
    Muthu

  9. Nirupama Says:

    Hi,

    I am also getting the same error and I have asked DBA to extend the TEMP table space they have made it to 50% more but still I am facing same issue.

    Can any one please help me to fix my issue.

    Thanks,
    Nirupama

  10. shariq Says:

    Needed to extend temporary tablesapce in my case alter database tempfile ‘/u02/oradata/TESTDB/temp01.dbf’ resize 250M;


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
Follow

Get every new post delivered to your Inbox.

Join 25 other followers