Database Utilities (MOSC)

MOSC Banner

ORA-20000 running dbms_stats.gather_schema

Statatistics collect with options   => 'GATHER AUTO', return error ORA-20000 in externaly table, check logs


14:28:30 SQL> BEGIN

14:28:30  2   DBMS_STATS.gather_schema_stats (

14:28:30  3     ownname      => DBMS_UTILITY.OLD_CURRENT_SCHEMA,

14:28:30  4     options      => 'GATHER AUTO',

14:28:30  5     estimate_percent  => '30',

14:28:30  6     method_opt     => 'FOR ALL INDEXED COLUMNS SIZE SKEWONLY',

14:28:30  7     degree       => 5,

14:28:30  8     GRANULARITY    => 'ALL',

14:28:30  9     CASCADE      => TRUE);

14:28:30 10 END;

14:28:30 11 /

BEGIN

*

ERROR at line 1:

ORA-20000: Statistics collection failed for 4 objects in the schema

ORA-06512: at "SYS.DBMS_STATS", line 36053

ORA-06512: at line 2


14:43:38 SQL> DECLARE

14:43:39  2   staleObjects  SYS.DBMS_STATS.ObjectTab;

14:43:39  3   staleObject  SYS.DBMS_STATS.ObjectElem;

14:43:39  4 BEGIN

14:43:39  5   SYS.DBMS_STATS.GATHER_SCHEMA_STATS (DBMS_UTILITY.OLD_CURRENT_SCHEMA,

14:43:39  6                     options  => 'LIST AUTO',

14:43:39  7                     objlist  => staleObjects);

14:43:39  8

14:43:39  9   FOR indx IN 1 .. staleObjects.COUNT

Howdy, Stranger!

Log In

To view full details, sign in to My Oracle Support Community.

Register

Don't have a My Oracle Support Community account? Click here to get started.

Category Leaderboard

Top contributors this month

New to My Oracle Support Community? Visit our Welcome Center

MOSC Help Center