ORA-20000: Unable to gather statistics concurrently: insufficient privileges

Error

exec DBMS_STATS.GATHER_TABLE_STATS('SPARKREF','TASK');

BEGIN DBMS_STATS.GATHER_TABLE_STATS('SPARKREF','TASK'); END;
Error at line 1
ORA-20000: Unable to gather statistics concurrently: insufficient privileges
ORA-06512: at "SYS.DBMS_STATS", line 34757

ORA-06512: at line 1

Cause

"ORA-20000: Unable to gather statistics concurrently, insufficient privileges" indicates that concurrent statistics gather option is enabled but failed due to insufficient privileges.

The User executing the DBMS_STATS.GATHER_TABLE_STATS does not have adequate privileges to perform 'concurrent statistics gathering'.

'concurrent statistics gathering' is new  from Oracle Database 11g Release 2 (11.2.0.2).

Solution

Execute one of the following options and then try again.

1. Grant following privileges to the user to enable them to run gather_stats in concurrent mode

GRANT CREATE JOB, MANAGE SCHEDULER, MANAGE ANY QUEUE to SPARK_REF_ADMIN;

OR

2. Disable the 'concurrent statistics gathering' feature using the following commands.

exec DBMS_STATS.SET_GLOBAL_PREFS(pname=>'CONCURRENT',pvalue=>'FALSE');