Hi
I created a procedure to gather stats for a specific table in the FR schema as below.
I granted execute to the procedure to schema FR_USER.
An exception however occurs when running the procedure from FR_USER.
Can anybody suggest why this happens?
SQL> select * from v$version;
BANNER
----------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE 11.2.0.4.0 Production
TNS for Linux: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production
SQL> conn fr/password@u2
Connected.
SQL> exec dbms_stats.gather_table_stats(sys_context('userenv','current_schema'), 'INSTR_E');
PL/SQL procedure successfully completed.
SQL> create or replace procedure test_table_stats_other_user(p_tbl in varchar2) as
2 begin
3 dbms_stats.gather_table_stats(sys_context('userenv','current_schema'), p_tbl);
4 end test_table_stats_other_user;
5 /
Procedure created.
SQL> grant execute on test_table_stats_other_user to fr_user;
Grant succeeded.
When I connect as FR_USER, I get the following exception however:
SQL> conn fr_user/password@u2
Connected.
SQL> exec fr.test_table_stats_other_user('INSTR_E');
BEGIN fr.test_table_stats_other_user('INSTR_E'); END;
*
ERROR at line 1:
ORA-20000: Unable to analyze TABLE "FR"."INSTR_E", insufficient
privileges or does not exist
ORA-06512: at "SYS.DBMS_STATS", line 24270
ORA-06512: at "SYS.DBMS_STATS", line 24332
ORA-06512: at "FCR.TEST_TABLE_STATS_OTHER_USER", line 3
ORA-06512: at line 1
Preferences are set as follows:
SQL> set linesize 200
SQL> set pagesize 200
SQL> column val format a30
SQL> column pname format a30
SQL> select dbms_stats.get_prefs('AUTOSTATS_TARGET') val , 'AUTOSTATS_TARGET' pname from dual union all
2 select dbms_stats.get_prefs('CASCADE') , 'CASCADE' from dual union all
3 select dbms_stats.get_prefs('CONCURRENT') , 'CONCURRENT' pname from dual union all
4 select dbms_stats.get_prefs('DEGREE') , 'DEGREE' pname from dual union all
5 select dbms_stats.get_prefs('ESTIMATE_PERCENT') , 'ESTIMATE_PERCENT' pname from dual union all
6 select dbms_stats.get_prefs('METHOD_OPT') , 'METHOD_OPT' pname from dual union all
7 select dbms_stats.get_prefs('NO_INVALIDATE') , 'NO_INVALIDATE' pname from dual union all
8 select dbms_stats.get_prefs('GRANULARITY') , 'GRANULARITY' pname from dual union all
9 select dbms_stats.get_prefs('PUBLISH') , 'PUBLISH' pname from dual union all
10 select dbms_stats.get_prefs('INCREMENTAL') , 'INCREMENTAL' pname from dual union all
11 select dbms_stats.get_prefs('STALE_PERCENT') , 'STALE_PERCENT' pname from dual union all
12 select dbms_stats.get_prefs('TABLE_CACHED_BLOCKS') , 'TABLE_CACHED_BLOCKS' pname from dual;
VAL PNAME
------------------------------ ------------------------------
AUTO AUTOSTATS_TARGET
DBMS_STATS.AUTO_CASCADE CASCADE
TRUE CONCURRENT
NULL DEGREE
DBMS_STATS.AUTO_SAMPLE_SIZE ESTIMATE_PERCENT
FOR ALL COLUMNS SIZE AUTO METHOD_OPT
DBMS_STATS.AUTO_INVALIDATE NO_INVALIDATE
AUTO GRANULARITY
TRUE PUBLISH
TRUE INCREMENTAL
10 STALE_PERCENT
1 TABLE_CACHED_BLOCKS
12 rows selected.