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

[gopu_g]

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

[PAVB]

Check fragmentation.

[gopu_g]

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

[gopu_g]

Hi DBA's

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

[vnktummala]

Thanks for the update Gopu.

Thanks,

[gopu_g]

You are welcome friend, Its my pleasure to share with you guys

[gopu_g]

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

[gandolf989]

Did someone check the DBA_recycle bin?

[gopu_g]

Did someone check the DBA_recycle bin?

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

[gandolf989]

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.

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.