#101 Oracle Schema build reports ORA-20000: Unable to gather statistics concurrently

v1.0_(example)
closed-wont-fix
None
9
2014-08-22
2014-02-27
Steve Shaw
No

On gathering schema statistics (in Oracle 12c or 11gR2) the following error is reported:

ORA-20000: Unable to gather statistics concurrently.

A user can’t gather statistics on its own table concurrently. However it is set to OFF by default (http://www.oracle.com/technetwork/database/bi-datawarehousing/twp-statistics-concepts-12c-1963871.pdf).

SQL> connect tpcc/tpcc
Connected.
SQL> select dbms_stats.get_prefs('concurrent') from dual;

DBMS_STATS.GET_PREFS('CONCURRENT')

OFF

to see this issue the sys user for the database needs to have:
1. Enabled resource manager (SQL> alter system set resource_manager_plan = 'DEFAULT_PLAN' scope=spfile;)
2. Set job_queue_processes to 4 (ORA-20000: Unable to gather statistics concurrently: the job_queue_processes parameter is less than 4
SQL> alter system set job_queue_processes=4;
System altered.)
3. Run : exec dbms_stats.set_global_prefs('CONCURRENT','TRUE'); (SQL> exec dbms_stats.set_global_prefs('CONCURRENT','TRUE'); PL/SQL procedure successfully completed.)

This results in the error when gathering statistics:

SQL> exec dbms_Stats.gather_schema_stats('TPCC');
BEGIN dbms_Stats.gather_schema_stats('TPCC'); END;
*
ERROR at line 1:
ORA-20000: Unable to gather statistics concurrently: insufficient privileges
ORA-06512: at "SYS.DBMS_STATS", line 35130
ORA-06512: at "SYS.DBMS_STATS", line 35153
ORA-06512: at line 1

To resolve this issue either before the schema build as SYS run:

exec dbms_stats.set_global_prefs('CONCURRENT','FALSE');

SQL> exec dbms_stats.set_global_prefs('CONCURRENT','FALSE');
PL/SQL procedure successfully completed.

SQL> select dbms_stats.get_prefs('concurrent') from dual;

DBMS_STATS.GET_PREFS('CONCURRENT')

FALSE

OR if the schema build has completed without statistics then:

Grant TPCC the DBA role as well as privileges CREATE JOB, MANAGE SCHEDULER, MANAGE ANY QUEUE.

and as TPCC re-run:

SQL> exec dbms_Stats.gather_schema_stats('TPCC');

PL/SQL procedure successfully completed.

Discussion

Get latest updates about Open Source Projects, Conferences and News.

Sign up for the SourceForge newsletter:





No, thanks