Getting the following errors when running a scheduled job to analyze schema stats :
Error:-
ORA-20000: Insufficient privileges to analyze an object in Schema
ORA-06512: at "SYS.DBMS_STATS", line 24867
ORA-06512: at "SYS.DBMS_STATS", line 24945
ORA-06512: at "SYS.DBMS_STATS", line 24899
ORA-06512: at "SYS.P_GATHER_SCHEMA_STATS1", line 8
ORA-20000: Insufficient privileges to analyze an object in Schema
ORA-06512: at "SYS.DBMS_STATS", line 24867
ORA-06512: at "SYS.DBMS_STATS", line 24945
ORA-06512: at "SYS.DBMS_STATS", line 24899
ORA-06512: at "SYS.P_GATHER_SCHEMA_STATS1", line 8
Cause:
SYSTEM user doesn't have the privilege to analyze any non system table(Other schema’s table).
Procedure:-
create or replace
procedure p_gather_schema_stats1
as
begin
--live projects
DBMS_STATS.GATHER_SCHEMA_STATS(ownname=>'LSC_COD_VODACO_03_SDP',CASCADE=>TRUE,ESTIMATE_PERCENT=>dbms_stats.auto_sample_size);
end;
create or replace
procedure p_gather_schema_stats1
as
begin
--live projects
DBMS_STATS.GATHER_SCHEMA_STATS(ownname=>'LSC_COD_VODACO_03_SDP',CASCADE=>TRUE,ESTIMATE_PERCENT=>dbms_stats.auto_sample_size);
end;
Solution:-
To implement the solution, please execute the following steps:
To implement the solution, please execute the following steps:
[oracle@lscukdb01 ~]$ sqldba
SQL*Plus: Release 11.2.0.3.0 Production on Wed Oct 9 05:37:13 2013
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
SQL> grant ANALYZE ANY to system;
Grant succeeded.
SQL> grant SELECT ANY TABLE to system;
SQL*Plus: Release 11.2.0.3.0 Production on Wed Oct 9 05:37:13 2013
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
SQL> grant ANALYZE ANY to system;
Grant succeeded.
SQL> grant SELECT ANY TABLE to system;
Note:- system is User running Job.
please comment it..
please comment it..
thanks!
ReplyDelete