package下 ANALYZE ANY 权限不足 dbms_stats.gather_table_stats

You are executing DBMS_STATS from within PL/SQL code.

This raises ORA-20000: extension not created: insufficient error.

CREATE TABLESPACE tbs_def DATAFILE 'tbs_def.dbf' SIZE 500M NOLOGGING;

create user test_runner identified by "1" default tablespace tbs_def quota 250M on tbs_def;

create user test_owner  identified by "1" default tablespace tbs_def quota 250M on tbs_def;

    GRANT    CREATE    ANY    CONTEXT        TO    test_runner;    
    GRANT    DROP    PUBLIC    SYNONYM    TO    test_runner;    
    GRANT    CREATE    TABLE            TO    test_runner;        
    GRANT    ALTER    SYSTEM            TO    test_runner;        
    GRANT    CREATE    JOB                TO    test_runner;        
    GRANT    CREATE    TYPE            TO    test_runner;        
    GRANT    CREATE    VIEW            TO    test_runner;        
    GRANT    CREATE    PUBLIC    SYNONYM    TO    test_runner;    
    GRANT    SELECT    ANY    DICTIONARY    TO    test_runner;    
    GRANT    SELECT    ANY    TABLE        TO    test_runner;    
    GRANT    ADMINISTER    SQL    MANAGEMENT    OBJECT    TO    test_runner;
    GRANT    CREATE    PROCEDURE        TO    test_runner;        
    GRANT    UNLIMITED    TABLESPACE    TO    test_runner;        
    GRANT    CREATE    SESSION            TO    test_runner;        
    GRANT    ANALYZE    ANY                TO    test_runner;        
    GRANT    CREATE    SEQUENCE        TO    test_runner;        
    GRANT    ANALYZE    ANY    DICTIONARY    TO    test_runner;    
    GRANT    CREATE    TRIGGER            TO    test_runner;        
    GRANT    EXECUTE    on    DBMS_LOCK   TO    test_runner;    
    GRANT    EXECUTE    on    SYS.DBMS_SHARED_POOL    TO    test_runner;    
    GRANT    SELECT    ANY    TABLE        TO    test_runner;    
    GRANT    MANAGE    SCHEDULER        TO    test_runner;        
    GRANT    CREATE    ANY    JOB            TO    test_runner;    
    GRANT    ALTER ANY TABLE            TO    test_runner;    

 alter session set current_schema= test_owner;
 
 create table test_tab (scompid varchar2(4000), kentity number, srolecompid varchar2(4000), krole number, nkey number);

 insert into test_tab values('00030', -5, 'NL', 3,0);

 commit;
 
 alter session set current_schema = test_runner;
 
 create or replace package test_pkg
 is
 
 procedure run_test;
 
 end;
 /
 
 create or replace package body test_pkg
 is
 
 procedure call_stats
 is
 begin
   dbms_stats.gather_table_stats(
   ownname=>'TEST_OWNER',
   tabname=>'TEST_TAB',
   CASCADE=>true,
   DEGREE=>4,
   ESTIMATE_PERCENT=>dbms_stats.auto_sample_size,
   METHOD_OPT=>'FOR COLUMNS SIZE AUTO (SCOMPID,KENTITY,KROLE)');
 end call_stats;
 
 procedure run_test
 is
 begin
   call_stats;
 end run_test;
 
 end;
 /

alter session set current_schema = test_runner;

ERROR at line 1:
ORA-20000: extension not created: insufficient
privileges:(SCOMPID,KENTITY,KROLE)
ORA-06512: at "SYS.DBMS_STATS", line 40799
ORA-06512: at "SYS.DBMS_STATS", line 40083
ORA-06512: at "SYS.DBMS_STATS", line 38960
ORA-06512: at "SYS.DBMS_STATS", line 15700
ORA-06512: at "SYS.DBMS_STATS", line 22064
ORA-06512: at "SYS.DBMS_STATS", line 22162
ORA-06512: at "SYS.DBMS_STATS", line 22232
ORA-06512: at "SYS.DBMS_STATS", line 22864
ORA-06512: at "SYS.DBMS_STATS", line 38359
ORA-06512: at "SYS.DBMS_STATS", line 39786
ORA-06512: at "SYS.DBMS_STATS", line 40231
ORA-06512: at "SYS.DBMS_STATS", line 40780
ORA-06512: at "TEST_RUNNER.TEST_PKG", line 7
ORA-06512: at "TEST_RUNNER.TEST_PKG", line 19
ORA-06512: at line 2

CHANGES

 Upgraded or created DB with version 12.2.0.1 or higher.

CAUSE

 Missing security rights for user that is executing DBMS_STATS from PL/SQL.

SOLUTION

Grant the needed security rights:

grant create any table to test_runner;

grant alter any table to test_runner;

SQL> conn sys/...... as sysdba
Connected.
SQL> grant create any table to test_runner;

Grant succeeded.

SQL> grant alter any table to test_runner;

Grant succeeded.

SQL>  alter session set current_schema = test_runner;

Session altered.

SQL> begin
  test_pkg.run_test;
end;
/  2    3    4

PL/SQL procedure successfully completed.