ORA-01652: unable to extend
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 19

Thread: ORA-01652: unable to extend

  1. #1
    Join Date
    Jan 2004
    Location
    Washington , USA
    Posts
    132

    ORA-01652: unable to extend

    Thanks in advance

    Tablespace is having almost 2.9G free space.

    select initial_extent,next_extent from dba_tablespaces where tablespace_name='PRONTOMSP';

    INITIAL_EXTENT NEXT_EXTENT
    -------------- -----------
    10485760 10485760

    i dont know why it is giving error. Any clues in this. ?

    ORA-01652: unable to extend temp segment by 61446 in tablespace PRONTOMSP

  2. #2
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,448
    that next_extent is not useful, show the next extent of the segment which is failing

    and show the maximum free extent size

  3. #3
    Join Date
    Jan 2004
    Location
    Washington , USA
    Posts
    132
    pando boss -

    first part of question i didnt get -

    second part of question i am posting the output
    SQL> select INITIAL_EXTENT,NEXT_EXTENT,MIN_EXTENTS,MAX_EXTENTS,PCT_INCREASE,STATUS from dba_tablespaces where tablespace_name='PRONTOMSP';

    INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS PCT_INCREASE STATUS
    -------------- ----------- ----------- ----------- ------------ ---------
    10485760 10485760 1 249 50 ONLINE

  4. #4
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,448
    what operation is causing this error

    ORA-01652: unable to extend temp segment by 61446 in tablespace PRONTOMSP

    Show which segment is causing that error and show us the next_extent of that segment

  5. #5
    Join Date
    Jan 2004
    Location
    Washington , USA
    Posts
    132
    objective is huge 10 tables from our database are TO BE partitioned

    when an alter statment for adding partition is executed at sql prompt this error comes.

    ORA-01652: unable to extend temp segment by 61446 in tablespace PRONTOMSP

    pando boss , i dont know how to find this.
    Show which segment is causing that error and show us the next_extent of that segment

    can u please tell me.

  6. #6
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,331
    select the next_extent for the *table* not the tablespace as the table settings can override the tablespace settings. Also check to see if you have enough contiguous free space in a file for that next extent

  7. #7
    Join Date
    Jan 2001
    Posts
    3,131
    Max extents on the table?
    I remember when this place was cool.

  8. #8
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,448
    Quote Originally Posted by gajananpkini
    when an alter statment for adding partition is executed at sql prompt this error comes.

    ORA-01652: unable to extend temp segment by 61446 in tablespace PRONTOMSP
    Which partition is causing the error? Partition is the segment then just look dba_segments for next_extent

  9. #9
    Join Date
    Jul 2000
    Posts
    521
    I guess this error must be happening when you are trying to "create" an object in the tablespace. Possibly an index. And, the operation is failing as it can not find enough usable free space in the tablespace. Is this a locally managed tablespace ? If not, the free space of 2.9 G may not be entirely usable. Check the extent size specs of the object being created and see how many extents bigger than those sizes are available free in the tablespace.
    svk

  10. #10
    Join Date
    Jan 2004
    Location
    Washington , USA
    Posts
    132

    ORA-1653: unable to extend table

    thanks in advance

    ORA-1653: unable to extend table QADB0703.PRONTO_ALERT by 466608 in tablespace OSSBSS

    i AM GETTING THIS ERROR

    details

    free space in this tablespace is 16gb.

    SQL> select INITIAL_EXTENT,NEXT_EXTENT,MIN_EXTENTS,MAX_EXTENTS from dba_Segments where SEGMENT_NAME='PRONTO_ALERT';

    INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS
    -------------- ----------- ----------- -----------
    20480 20480 1 249
    20480 1911226368 1 249


    select INITIAL_EXTENT,NEXT_EXTENT,MIN_EXTENTS,MAX_EXTENTS from dba_Segments where TABLESPACE_NAME='OSSBSS';
    INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS
    -------------- ----------- ----------- -----------
    20480 20480 1 249
    20480 20480 1 249
    20480 20480 1 249
    20480 20480 1 249
    20480 20480 1 249

    WHAT SHOULD I DO?

    shall i increase the initial & next to 10m each

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width