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
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.
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';
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
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.
Reply With Quote
Originally Posted by gandolf989
Bookmarks