Search my Blog

Follow me on Twitter

When truncating a table does not work ORA-01426

Introduction.

This mornings incident brought a big smile to my face so i thought highest time to share it with the community again. I got a call from a colleague who takes care of an application that he had issues with the 11.1 Oracle environment. He explained to me that he tried to truncate a table and that in stead of being rewarded with an empty table he got punished with a ORA-01426. As often the web was a Dbas best friend again so the puzzle got solved.

Details:

This incident had two staging tables. Both of them had 1,000,000,000 ( that is right that is 1,000 million rows this is not a typo) , and Oracle would not allow the Application / user to truncate that staging table in one blow by truncating it because it brought ORA-01426 the horror! Hmm do i sound sarcastic yet cause frankly i was bobbing head when hearing these details.

Anyhow as always Internet ( Metalink too) is your friend so in the end we had two options:

a. Flush in-memory monitoring information for all tables in the dictionary.

exec DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO()

b. For problematic table set a small value for NUMROWS using
## for both tables in scope
exec DBMS_STATS.SET_TABLE_STATS( ‘MYUSER’,’TABLE_UPLD2′,NUMROWS=> 10000 )
exec DBMS_STATS.SET_TABLE_STATS( ‘MYUSER’,’TABLE_UPLD1′,NUMROWS=> 10000 )

Given the fact that this was a production issue i performed workaround and recommended customer to check with his software provider if this was an out of control cleanup issue of staging tables or just a bug in the application software.