Oracle 11.2.0.4.0 StdEd-One 64-bit
Windows 2012
SQL> --
SQL> show user
USER is "MYUSER"
SQL> begin
2 dbms_stats.gather_database_stats(estimate_percent => 20, method_opt => 'FOR ALL COLUMNS SIZE SKEWONLY', options => 'GATHER EMPTY', no_invalidate => false);
3 end;
4 /
PL/SQL procedure successfully completed.
SQL> begin
2 dbms_stats.gather_database_stats(ownname => 'MYUSER', estimate_percent => 20, method_opt => 'FOR ALL COLUMNS SIZE SKEWONLY', options => 'GATHER EMPTY', no_invalidate => false);
3 end;
4 /
dbms_stats.gather_database_stats(ownname => 'MYUSER', estimate_percent => 20, method_opt => 'FOR ALL COLUMNS SIZE SKEWONLY', options => 'GATHER EMPTY', no_invalidate => false);
*
ERROR at line 2:
ORA-06550: line 2, column 1:
PLS-00306: wrong number or types of arguments in call to 'GATHER_DATABASE_STATS'
ORA-06550: line 2, column 1:
PL/SQL: Statement ignored
Only difference is addition of 'ownname' parameter. I can't see any reason for that to fail.
Second item - If I (as 'myuser') schedule a job to use the first construct, it fails with insufficient privileges:
SQL> --
SQL> select owner,
2 job_name,
3 job_type,
4 job_action
5 from dba_scheduler_jobs
6 where owner='MYUSER'
7 and job_name='APPLICATION_STATS'
8 ;
OWNER JOB_NAME JOB_TYPE JOB_ACTION
--------------- ------------------ ---------------- ----------------------------------------------------------------------
MYUSER APPLICATION_STATS PLSQL_BLOCK begin
dbms_stats.gather_database_stats(estimate_percent => 20, method_opt =>
'FOR ALL COLUMNS SIZE SKEWONLY', options => 'GATHER EMPTY', no_invali
date => false);
dbms_stats.gather_database_stats(estimate_percent => 20, method_opt =>
'FOR ALL COLUMNS SIZE SKEWONLY', options => 'GATHER STALE', no_invali
date => false);
end;
1 row selected.
SQL> --
SQL> select log_id,
2 status,
3 additional_info
4 from dba_scheduler_job_run_details
5 where owner='MYUSER'
6 and job_name='APPLICATION_STATS'
7 ;
LOG_ID STATUS ADDITIONAL_INFO
---------- ---------- ----------------------------------------------------------------------
736902 FAILED ORA-20000: Insufficient privileges to analyze an object in Database
ORA-06512: at "SYS.DBMS_STATS", line 25335
ORA-06512: at "SYS.DBMS_STATS", line 25511
ORA-06512: at "SYS.DBMS_STATS", line 25467
ORA-06512: at line 3
1 row selected.
So, connect as 'myuser' I can execute the procedure, but if 'myuser' creates a job to execute the procedure, it fails with insufficient privs. (mumbling and drooling in my oatmeal .. )