ORA-20000: Unable to gather statistics concurrently: Resource manager plan is...

jiawang

一、现象

    While attempting to gathering statistics on Oracle 12c, receive ORA-20000 error as follows:

SQL> begin dbms_stats.gather_schema_stats(  ownname=> 'CS',cascade=> DBMS_STATS.AUTO_CASCADE, estimate_percent=> DBMS_STATS.AUTO_SAMPLE_SIZE,
2  degree=> 8,no_invalidate=> DBMS_STATS.AUTO_INVALIDATE,  granularity=> 'AUTO',method_opt=> 'FOR ALL INDEXED COLUMNS SIZE AUTO',  options=> 'GATHER
');end;
3
4  /
begin dbms_stats.gather_schema_stats(  ownname=> 'CS',cascade=> DBMS_STATS.AUTO_CASCADE, estimate_percent=> DBMS_STATS.AUTO_SAMPLE_SIZE,
*

ERROR at line 1:
ORA-20000: Unable to gather statistics concurrently: Resource manager plan is
not active or is not managing CPU usage
ORA-06512: at "SYS.DBMS_STATS", line 35991
ORA-06512: at line 1

    Database was recently upgraded from 11.2 to 12c with concurrent setting to true in both versions

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

DBMS_STATS.GET_PREFS('CONCURRENT')
--------------------------------------------------------------------------------
TRUE

    Statistics Gathering worked fine on the earlier version

二、解决方法

To avoid the error, either Enable Resource Manager OR Disable concurrent statistics gathering by setting the CONCURRENT preference to OFF. You will need to restart the database to enable these changes.

以下方法二选一：

Enable Resource Manager:

SQL> alter system set resource_manager_plan = 'DEFAULT_PLAN' scope=spfile;

Disable concurrent statistics gathering by setting the CONCURRENT preference to OFF:

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