Thanks for the question, Sridhar.
Asked: August 21, 2017 - 1:22 pm UTC
Last updated: August 21, 2017 - 3:31 pm UTC
Version: 11.2.0.4.0
Viewed 1000+ times
You Asked
Hi Oracle Masters,
We run End of Day (EOD) for our bank, and once in a while the EOD process aborts with the following error:
ORA-01683: unable to extend index XXXXXX.PK01_TABLE_NAME partition P_1132 by 1024 in tablespace TBLSPCNAME.
This kind of aborts delays our EOD processing and other subsequent banking processes. Is there a query or a tool which we can use to foresee this error and take action (like a Pre-check for EOD). We run gather stats for the aforementioned table once a month.
Environment Details:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, Oracle Label Security,
OLAP, Data Mining, Oracle Database Vault and Real Application Testing options
Kindly let me know if you need more details.
Regards,
Sri
and Chris said...
Gathering stats won't help you here.
You should put in place some monitoring that alerts you when a tablespace has less than X% or YGb free left. Exactly what values you pick for these thresholds depends on how much your database grows each day/week/month, how consistent this growth is and how much you want to avoid being called out for this error ;)
Any decent monitoring tool should be able to let you setup these warnings and email you when you exceed them.
Or you can roll your own using scripts like:
https://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:285415955510