How to resolve ORA-20000 Error during concurrent statistics

Recently I have faced an issue when application team is executing gather stats in their job.

An application job is loading the data into tables and after that it is performing gather stats. They received below error during gather stats

ERROR at line 1:
ORA-20000: Unable to gather statistics concurrently: insufficient privileges
ORA-06512: at “SYS.DBMS_STATS”, line 34634
ORA-06512: at line 1

There are two steps to resolve this issue.

Step 1: Disabling Global preferences

check for the stats global preference value using below query

SELECT DBMS_STATS.get_prefs(‘CONCURRENT’) FROM dual;

DBMS_STATS.GET_PREFS(‘CONCURRENT’)
—————————————————————————————————-
OFF

By output, we can understand that it is OFF and we can turn it on

BEGIN
DBMS_STATS.set_global_prefs (
pname => ‘CONCURRENT’,
pvalue => ‘ALL’);
END;
/

Instead of ALL (which works for both manual and automatic stats collection), we can also set below values
MANUAL – only for manual stats collection
AUTOMATIC – only for automatic stats collection

Step 2: Granting roles to the user

If you see that Global preferences value is already set to ALL, you need to grant below mentioned roles to the user which is performing gather stats.
These grants are not default, so users will face issues if they use concurrent statistics.

SQL> GRANT CREATE JOB, MANAGE SCHEDULER, MANAGE ANY QUEUE TO testuser;

Once both of above steps are done, issue is resolved.