ORA-01653: unable to extend table by 128 in tablespace SYSUTIL
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 17

Thread: ORA-01653: unable to extend table by 128 in tablespace SYSUTIL

  1. #1
    Join Date
    Aug 2007
    Location
    Cyberjaya,kuala lumpur
    Posts
    339

    ORA-01653: unable to extend table by 128 in tablespace SYSUTIL

    We have received below ORA-01653 error in an 11.2.0.1.0 production DB, we have enough space in the tablespace and the EXTENT_MANAGEMENT is set it as LOCAL, Could you please advice what may be root cause of this incident and fix for the same?

    ORA-01653: unable to extend table ORAAUDIT.AUDIT_DDL_LOG by 128 in tablespace SYSUTIL

    TABLESPACE_NAME Tot size MB Tot Free MB %FREE %USED
    ------------------------- ----------- ----------- ---------- ----------
    SYSUTIL 1000 833 83 17
    Thanks/Gopu

  2. #2
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    Check fragmentation.
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  3. #3
    Join Date
    Aug 2007
    Location
    Cyberjaya,kuala lumpur
    Posts
    339
    Yes PAVB
    we have done that in order to fix it temporarily

    But still I am wondering why locally managed tablespace is not able to find a large enough area of free contiguous space in which to fit the next extent , still we have 80% free space in the tablespace

    Current extent details for that object.

    SQL> select TABLE_NAME,next_extent,pct_increase from dba_tables where TABLE_NAME='AUDIT_DDL_LOG';

    TABLE_NAME NEXT_EXTENT PCT_INCREASE
    ------------------------------ --------------------- -------------------------
    AUDIT_DDL_LOG 1048576
    Last edited by gopu_g; 08-18-2011 at 05:01 AM.
    Thanks/Gopu

  4. #4
    Join Date
    Aug 2007
    Location
    Cyberjaya,kuala lumpur
    Posts
    339
    Hi DBA's

    This is happening due to Bug 12405549
    Currently Oracle Development team is working on it.
    Thanks/Gopu

  5. #5
    Join Date
    Mar 2006
    Location
    Charlotte, NC
    Posts
    865
    Thanks for the update Gopu.

    Thanks,
    Vijay Tummala

    Try hard to get what you like OR you will be forced to like what you get.

  6. #6
    Join Date
    Aug 2007
    Location
    Cyberjaya,kuala lumpur
    Posts
    339
    You are welcome friend, Its my pleasure to share with you guys
    Thanks/Gopu

  7. #7
    Join Date
    Aug 2007
    Location
    Cyberjaya,kuala lumpur
    Posts
    339
    I think we are getting ORA-1653 is not because of fragmentation.
    the issue is very similar to bug 12405549 and because of that we are hitting
    - ORA-1653 WHEN THERE IS MORE THAN 19GB FREE SPACE IN THE TABLESPACE
    Thanks/Gopu

  8. #8
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    3,999
    Did someone check the DBA_recycle bin?
    this space intentionally left blank

  9. #9
    Join Date
    Aug 2007
    Location
    Cyberjaya,kuala lumpur
    Posts
    339
    Quote Originally Posted by gandolf989 View Post
    Did someone check the DBA_recycle bin?

    No , Its really appreciated, if you could explain me how its related?
    Thanks/Gopu

  10. #10
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    3,999
    When you drop tables in Oracle 11g, the database renames the table making it a hidden table as well as all of the constraints. It does not free space in any tablespace. Then if someone drops a table and realizes that they should not have dropped the table, they can recover it from the recycle bin. You can turn off this feature database wide if you want. Here is the documentation on what it is and how it works.

    Code:
    http://download.oracle.com/docs/cd/B28359_01/server.111/b28310/tables011.htm
    So it could be that you need to do PURGE DBA_RECYCLEBIN; to free up space in your tablespace.
    this space intentionally left blank

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