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:
either patch it
Apply Patch 8226471
OR
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 )
c. Issue truncate/exchange partition statement.
exec DBMS_UTILITY.EXEC_DDL_STATEMENT(‘TRUNCATE TABLE TABLE_UPLD2’);
exec DBMS_UTILITY.EXEC_DDL_STATEMENT(‘TRUNCATE TABLE TABLE_UPLD1’);
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.
As always happy reading
Mathijs
Mathijs Bruggink 