WHEN OTHERS … sometimes just not enough

Posted by

We currently are working on a bug with Oracle Support with dbms_stats in

That may be the subject of a latter post, but in a nutshell, here’s the issue

SQL> exec dbms_stats.gather_table_stats('MY_SCHEMA', 'MY_SCHEMA');
BEGIN dbms_stats.gather_table_stats('MY_SCHEMA', 'MY_SCHEMA'); END;
ERROR at line 1:
ORA-21700: object does not exist or is marked for delete

Now obviously that’s not meant to be happening, and it pertains to incremental stats on a database that’s been upgraded from to More on that another time.

But as we work on the bug, a seemingly obvious measure would be to catch that exception and move on…So lets try that, in fact, lets be brutal and ignore all errors, mainly for the purpose of the example, but also to raise the hackles of good friend Tom 🙂

SQL> begin
  2    dbms_stats.gather_table_stats('MY_SCHEMA', 'MY_SCHEMA');
  3  exception
  4    when others then
  5       null;
  6  end;
  7  /
ERROR at line 1:
ORA-21700: object does not exist or is marked for delete


By the way, if you’re encountering this issue, delete your INCREMENTAL stats preference as a temporary workaround.


Got some thoughts? Leave a comment

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 )

Facebook photo

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

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.