Re: missing some sys.tables

Frank, what version of Oracle are you dealing with?

On 9.2.0.5 if I login as user 'SYS' a query of dba_objects turns up only 5 X$ objects but I am able to query hundreds of them.

> select owner, object_name, object_type
  2 from dba_objects
  3 where object_name like 'X$%'
  4
> col object_name format a30
> col owner format a12
> /

OWNER        OBJECT_NAME    OBJECT_TYPE
------------ ------------------------------      ------------------
PUBLIC       X$KCBFWAIT          SYNONYM
PUBLIC       X$KSLLT                 SYNONYM
PUBLIC       X$KSPPI                 SYNONYM
PUBLIC       X$KSPPSV             SYNONYM
PUBLIC       X$KSQST                SYNONYM

> select count(*) from sys.x$kglcursor;

  COUNT(*)

      2792

The views are there. There are just hidden better. By default only user sys can reference the x$ objects so using the user sys id I have exposed each x$ view with a generated view of select * from x$ where I rename the view to xx_. I granted select on these to the DBA role so that I can reference these views as sys.xx_ without having to use the sys id.

I just modify scripts I find or write to use my sys.xx_ version of the x$ view and with every upgrade rerun my view generation script to pick up changes and reissue the grants.

HTH -- Mark D Powell -- Received on Thu Feb 24 2005 - 09:01:16 CST