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');

ORA-39126: Worker unexpected fatal error in KUPW$WORKER.STATS_UNLOAD [MARKER]

Error:

Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/STATISTICS/MARKER
ORA-39126: Worker unexpected fatal error in KUPW$WORKER.STATS_UNLOAD [MARKER]
MARKER
ORA-01000: maximum open cursors exceeded
ORA-06512: at "SYS.DBMS_STATS_INTERNAL", line 15556
ORA-06512: at "SYS.DBMS_STATS_INTERNAL", line 15568
ORA-02063: preceding 3 lines from PROD2DELTA

ORA-06512: at "SYS.DBMS_SYS_ERROR", line 105
ORA-06512: at "SYS.KUPW$WORKER", line 11265

----- PL/SQL Call Stack -----
  object      line  object
  handle    number  name
0x17ba1deff8     27116  package body SYS.KUPW$WORKER
0x17ba1deff8     11286  package body SYS.KUPW$WORKER
0x17ba1deff8     24429  package body SYS.KUPW$WORKER
0x17ba1deff8     20692  package body SYS.KUPW$WORKER
0x17ba1deff8     10206  package body SYS.KUPW$WORKER
0x17ba1deff8     13381  package body SYS.KUPW$WORKER
0x17ba1deff8      3173  package body SYS.KUPW$WORKER
0x17ba1deff8     12035  package body SYS.KUPW$WORKER
0x17ba1deff8      2081  package body SYS.KUPW$WORKER
0xd897ec40         2  anonymous block

Fixing up the name in the impdp stat table
ORA-39126: Worker unexpected fatal error in KUPW$WORKER.STATS_LOAD [MARKER]
ORA-30926: unable to get a stable set of rows in the source tables

ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at "SYS.KUPW$WORKER", line 11259

----- PL/SQL Call Stack -----
  object      line  object
  handle    number  name
0x17ba1deff8     27116  package body SYS.KUPW$WORKER
0x17ba1deff8     11286  package body SYS.KUPW$WORKER
0x17ba1deff8     24286  package body SYS.KUPW$WORKER
0x17ba1deff8     24415  package body SYS.KUPW$WORKER
0x17ba1deff8     10105  package body SYS.KUPW$WORKER
0x17ba1deff8     13381  package body SYS.KUPW$WORKER
0x17ba1deff8      3173  package body SYS.KUPW$WORKER
0x17ba1deff8     12035  package body SYS.KUPW$WORKER
0x17ba1deff8      2081  package body SYS.KUPW$WORKER
0xd897ec40         2  anonymous block

In STATS_UNLOAD
DBMS_STATS.EXPORT_STATS_FOR_DP
DBMS_STATS.EXPORT_STATS_FOR_DP
In STATS_LOAD with process_order 544
Fixing up the name in the impdp stat table

Job "EXPOPR"."SYS_IMPORT_SCHEMA_02" stopped due to fatal error.

Cause:

The issue is because of stale statistics.

Solution:

To overcome from this use the below workarounds:

1. Gather the statistics for Dictionary and Fixed Objects as follows:

SQL> exec dbms_stats.gather_dictionary_stats;
SQL> exec dbms_stats.lock_table_stats (null,'X$KCCLH');
SQL> exec dbms_stats.gather_fixed_objects_stats;

2. Run a fresh export after gathering stats then, use the new generated dump file to import.

OR

3. Exclude statistics during the import job and gather stats afterwards.

impdp system/password parfile=imp_user.par ...  exclude=statistics

Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT

Job "EXPOPR"."SYS_IMPORT_SCHEMA_03" completed

ORA-20100: Error: FND_FILE failure. Unable to create file

Error:

Oracle error 20100: java.sql.SQLException: ORA-20100: Error: FND_FILE failure. Unable to create file, o0971960.tmp in the directory, /usr/tmp.

You will find more information in the request log.

ORA-06512: at "APPS.FND_FILE", line 417

ORA-06512: at "APPS.FND_FILE", line 526

ORA-06512: at "APPS.FND_CONCURRENT", line 1335

ORA-06512: at line 1

 has been detected in FND_CONCURRENT.SET_INTERIM_STATUS.+---------------------------------------------------------------------------+

Start of log messages from FND_FILE

Cause:

The common root cause of this type of issue is multiple instances targeting the same physical directory that can make it full or .tmp file with same request number might exist.

Solution:

Each environment needs to target a separate utl_file_dir to prevent the recurrence of this issue.

This value can be observed with the following query.

select value from v$parameter where name = 'utl_file_dir';

The issue is resolved by creating some space or changing utl_file_dir to some new location where user has access to read and write.

1. Clear /usr/tmp folder, or the folder that is full

OR

2. Change utl_file_dir and $APPLTMP location to some new location.

3. Check the APPLPTMP variable value is the same as the first directory path defined in UTL_FILE_DIR

e.g. echo $APPLPTMP

2. Retest the issue