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
Check fragmentation.
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
Hi DBA's
This is happening due to Bug 12405549
Currently Oracle Development team is working on it.
Thanks for the update Gopu.
Thanks,
You are welcome friend, Its my pleasure to share with you guys :)
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
Did someone check the DBA_recycle bin? :confused:
Quote:
Originally Posted by gandolf989
No , Its really appreciated, if you could explain me how its related?
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.
So it could be that you need to do PURGE DBA_RECYCLEBIN; to free up space in your tablespace.Code:http://download.oracle.com/docs/cd/B28359_01/server.111/b28310/tables011.htm
You might also try alter tablespace SYSUTIL coalesce;
Check fragmentation???? How old fashioned!!!! ;)Quote:
Originally Posted by PAVB
Do we still use VT's for our terminal sessions? :p
Quote:
Originally Posted by gandolf989
Good answer, surely we will check it :)
Quote:
Originally Posted by gandolf989
We have tried that, but did't help, Even we have suggested the same from Oracle first and after that they have came up with this as a bug, so we are just waiting to release that, thanks a lot for your valuable answer, i will go with your suggestion.
You should check the PCT_INCREASE for the ORAAUDIT.AUDIT_DDL_LOG table. If it is set high it will try to grow the size of each new extent.
select PCT_INCREASE from dba_tables
where owner='ORAAUDIT' and
table_name='AUDIT_DDL_LOG';
Thanks gandolf
you made may day man - I'm an old fart - LOLQuote:
Originally Posted by gandolf989