1 Reply Latest reply: Jul 6, 2010 7:28 AM by fjfranken RSS

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

    user13364377
      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
          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