Insufficient privileges in 10g after conn / as sysdba!

[julian]

SQL> conn / as sysdba
Connected.
SQL> exec dbms_stats.gather_fixed_objects_stats('ALL');
BEGIN dbms_stats.gather_fixed_objects_stats('ALL'); END;

*
ERROR at line 1:
ORA-20000: Insufficient privileges to analyze an object in Fixed Ob
ORA-06512: at "SYS.DBMS_STATS", line 11802
ORA-06512: at "SYS.DBMS_STATS", line 12085
ORA-06512: at "SYS.DBMS_STATS", line 12599
ORA-06512: at line 1

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - 64bi
PL/SQL Release 10.1.0.2.0 - Production
CORE 10.1.0.2.0 Production
TNS for Solaris: Version 10.1.0.2.0 - Production
NLSRTL Version 10.1.0.2.0 - Production

What do you suggest??

[abhaysk]

Originally posted by julian
What do you suggest??

First lets understand why its needed? ( fixed table and stats ? ).. --- probably new in 10g.. but why

[julian]

Originally posted by abhaysk
First lets understand why its needed? ( fixed table and stats ? ).. --- probably new in 10g.. but why

OK, in 10g Oracle recoomends to analyze them but only once (unless the workload footprint changes).

So how about the problem: I have never run into Insufficient privileges error after logging as sysdba :-)

[zainalj]

wild guess, it is because fixed object cannot be analyzed.

[abhaysk]

zainalj :

GATHER_FIXED_OBJECTS_STATS Procedure
This procedure gathers statistics for all fixed objects (dynamic performance tables).

( Why do u think then oracle have given this package )?

[abhaysk]

Originally posted by julian
I have never run into Insufficient privileges error after logging as sysdba :-)

Jus check if "Analyze Any Dictionary" Privilage is available to SYS.. Probabaly even for SYS its not default and may have to grant urself with that :-)

Abhay.

[julian]

Originally posted by abhaysk
Jus check if "Analyze Any Dictionary" Privilage is available to SYS.. Probabaly even for SYS its not default and may have to grant urself with that :-)

Abhay.

Nope :-))

SQL> grant Analyze Any Dictionary to sys;

Grant succeeded.

SQL> exec dbms_stats.gather_fixed_objects_stats('ALL');
BEGIN dbms_stats.gather_fixed_objects_stats('ALL'); END;

*
ERROR at line 1:
ORA-20000: Insufficient privileges to analyze an object in Fixed Ob
ORA-06512: at "SYS.DBMS_STATS", line 11802
ORA-06512: at "SYS.DBMS_STATS", line 12085
ORA-06512: at "SYS.DBMS_STATS", line 12599
ORA-06512: at line 1

[abhaysk]

Originally posted by julian
OK, in 10g Oracle recoomends to analyze them but only once (unless the workload footprint changes)

Guess u r too optimistic abt CBO.. BTW, where does/ did oracle recomend to have stats on SYS/Fixed Tables? ( Link? )

[zainalj]

Originally posted by abhaysk
zainalj :

GATHER_FIXED_OBJECTS_STATS Procedure
This procedure gathers statistics for all fixed objects (dynamic performance tables).

( Why do u think then oracle have given this package )?

i think oracle 10g having a bulk of bug.

[julian]

Originally posted by zainalj
i think oracle 10g having a bulk of bug.

Could be, I opened an iTar with metalink. I was told to grant ANALYZE ANY DICTIONARY to me (i.e. SYS) :-) Of course, it did not help...