This discussion is archived
1 Reply Latest reply: Jul 6, 2010 5:28 AM by fjfranken RSS

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

user13364377 Newbie
Currently Being Moderated
I was trying to create a table in my schema with the syntax,

sql>create table test_tab as select * from sys.dba_objects;

ERROR at line 1:
ORA-01652: unable to extend temp segment by 128 in tablespace DATA

My tempfile is located at /oracle/product/dbn/temp01.dbf and its size was 30MB



Troubleshooting steps followed:

1.I have resized my tempfile to 1g and checked, but the error persists.

SQL> alter database tempfile '/oracle/product/dbn/temp01.dbf' resize 1g;

2.Then i created a temporary tablespace with 2G size and assigned it to my schema.

SQL> create temporary tablespace temper tempfile '/oracle/temper.dbf' size 2G;

Tablespace created.

SQL> alter user datas temporary tablespace temper;

User altered.

3. Then I logged into my schema and tried creating the table. but still the error persists.

SQL> create table test_tab as select * from dba_objects;
create table newww as select * from dba_objects
*
ERROR at line 1:
ORA-01652: unable to extend temp segment by 128 in tablespace DATA


4. Then i add a datafile with size 500M to my tablespace and tried creating the table. This time the error disappeared and i was able to create the table.

SQL> alter tablespace data add datafile '/oracle/datass.dbf' size 500M;

Tablespace altered.

SQL> conn datas
Enter password:
Connected.
SQL> create table newer as select * from sys.dba_objects;

Table created.

-----------------------------------------

My question here is, Is this error (ERROR at line 1:
ORA-01652: unable to extend temp segment by 128 in tablespace DATA) relates with the size of temporary tablespace or normal tablespace??????

and second, why is it accessing temporary tablespace while creating a table, temporary tablespace is accessed only when we do sort operations, right?



3. I have dropped the temporary tablespace I have created, but the space in the disk has not been released.

sql> drop tablespace temper including contents and datafiles;

Table dropped.

the datafile has been deleted from the disk, but when I query

$df -h

the released space is not showing up...

Please help.
  • 1. Re: ORA-01652: unable to extend temp segment by 128 in tablespace DATA
    fjfranken Expert
    Currently Being Moderated
    I think using Google would have given some answers:

    The fact that oracle complains about being unable to add TEMP segments often makes the DBA start searching into extending the TEMP tablespace.
    But it is smart to read until the end of the line. The problem then gets more clear.

    It is obvious to the statement that there is a lack of TEMP segments in the DATA tablespace. So not in your TEMP tablespace.
    This is also confirmed by the fact that your problem got solved by adding space to the DATA tablespace.

    Maybe you should consider to start using autoextend features.


    Regarding the second question:

    The database of the dropped temp tablespace might be still in use while e.g. SMON is cleaning up segments.
    After a while - but for sure if you bounce the db - the space will be released


    HTH

    FJFranken
    My Blog: http://managingoracle.blogspot.com

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points