Re: A potential bug (infinite loop) in Oracle: querying v$access

Charles Hooper wrote:
>
> The fixed object stats must be wrong (I recall having a problem with
> that a couple years ago when perfoming the following)...
> SQL> CONNECT / AS SYSDBA
> 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 17951
> ORA-06512: at "SYS.DBMS_STATS", line 18404
> ORA-06512: at "SYS.DBMS_STATS", line 18951
> ORA-06512: at line 1
>
> The same error occurs on Oracle 10.2.0.2, 10.2.0.3, and 11.1.0.6 as
> the internal user, SYS AS SYSDBA, and SYSTEM.
>
> There must be another way:
> SQL> EXEC DBMS_STATS.GATHER_SCHEMA_STATS('SYS',GATHER_FIXED=>TRUE)
>
> PL/SQL PROCEDURE SUCCESSFULLY COMPLETED.
>

Charles to the rescue:

SQL> EXEC DBMS_STATS.GATHER_SCHEMA_STATS('SYS',GATHER_FIXED=>TRUE); PL/SQL procedure successfully completed.

SQL> set timing on
SQL> select distinct owner from v$access;

OWNER

At least there's a workaround.

-- 

Regards,
Frank van Bortel

Top-posting in UseNet newsgroups is one way to shut me up