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:

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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s