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.