This discussion is archived
2 Replies Latest reply: Jul 28, 2011 12:54 PM by 878873 RSS

error ORA-20000 collecting statistics

Alain Newbie
Currently Being Moderated
on linux:
sqlplus / as sysdba

Connected to:
Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Beta

SQL> exec dbms_stats.gather_schema_stats('MYUSER',estimate_percent=>100,cascade=>true);
BEGIN dbms_stats.gather_schema_stats('MYUSER',estimate_percent=>100,cascade=>true); END;

*
ERROR at line 1:
ORA-20000: Statistics collection failed for all objects in schema
ORA-06512: at "SYS.DBMS_STATS", line 24189
ORA-06512: at "SYS.DBMS_STATS", line 24130
ORA-06512: at line 1

I tried: grant execute on SYS.DBMS_STATS to public, but it didn't help.

I also tried connected to myuser, no change.

Any hint?

Any hint?
  • 1. Re: error ORA-20000 collecting statistics
    Alain Newbie
    Currently Being Moderated
    found why in alert_log:
    DBMS_STATS: GATHER_STATS_JOB encountered errors. Check the trace file.
    Errors in file /u01/app/oracle/diag/rdbms/xe/XE/trace/XE_j001_2163.trc:
    ORA-00604: error occurred at recursive SQL level 2
    ORA-01502: index 'SYS.I_WRI$_OPTSTAT_TAB_OBJ#_ST' or partition of such index is in unusable state

    => alter index SYS.I_WRI$_OPTSTAT_TAB_OBJ#_ST rebuild;

    then it works.
  • 2. Re: error ORA-20000 collecting statistics
    878873 Newbie
    Currently Being Moderated
    Connected to:
    Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
    With the Partitioning, Real Application Clusters and Automatic Storage Management options


    SQL> begin

    dbms_stats.gather_schema_stats(
    ownname=> '"EPICEDF"' ,
    options=> 'GATHER AUTO');

    end;
    2 3 4 5 6 7 8
    9 /
    begin
    *
    ERROR at line 1:
    ORA-20000: Statistics collection failed for all objects in schema
    ORA-06512: at "SYS.DBMS_STATS", line 24189
    ORA-06512: at "SYS.DBMS_STATS", line 24130
    ORA-06512: at line 3

    I dont have any errors in the alert log or trace files

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points