2 Replies Latest reply: Jul 28, 2011 2:54 PM by 878873 RSS

    error ORA-20000 collecting statistics

    Alain
      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
          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
            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