Hi All,
I am using Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production. I have created a procedure that gathers table statistics using DBMS_STATS using AUTHID CURRENT_USER in SChema1.
CREATE OR REPLACE PROCEDURE SCHEMA1.getstats ( p_schema IN VARCHAR2,
p_table IN VARCHAR2)
AUTHID CURRENT_USER
AS
BEGIN
dbms_stats.gather_table_stats(ownname => p_schema,
tabname => p_table,
method_opt => 'FOR ALL COLUMNS SIZE AUTO',
degree => 8,
force => true,
no_invalidate => false);
END;
I am trying to use the same procedure to collect the stats for a table in another schema. However, I am getting the error ORA-20000: Unable to analyze TABLE "'SCHEMA2."DETAIL_VALUE", insufficient privileges or does not exist
EXEC getstats ( 'SCHEMA2','DETAIL_VALUE');
I have grant the privileges on that table to SCHEMA1. But still getting the error. I understand AUTHID CURRENT_USER will try to use as the procedure as own schema, but still I am getting the error. Can anyone help me on this?
GRANT ALL ON DETAIL_VALUE TO SCHEMA1