Snaphots / Materialized Views

[sumsar]

Hi

Is anybody able to view materialized views, using the Browser folder, Materialized Views?
I'm running a 8.1.7.4.0 database, and this does'nt
include snapshots/materalized views in the view all_objects.

Rasmus

 

[Marco Kalter]

It seems you are running into Oracle bug 1188948. Oracle supplies the following workaround:

Although there is no patch available for <Bug:1188948>, it can be solved manually
  by modifying the Dictionary views ALL_OBJECTS, DBA_OBJECTS and USER_OBJECTS in
  the 'catalog.sql' file (see $ORACLE_HOME/rdbms/admin).

  Modify the query part of the 'CREATE OR REPLACE VIEW ALL_OBJECTS' statement
  (similar to DBA_OBJECTS and USER_OBJECTS) in following way:

  CREATE OR REPLACE VIEW ALL_OBJECTS
  ...
  as
  select u.name, o.name, o.subname, o.obj#, o.dataobj#,
         decode(o.type#, 0, 'NEXT OBJECT', 1, 'INDEX', 2, 'TABLE', 3, 'CLUSTER',
                         4, 'VIEW', 5, 'SYNONYM', 6, 'SEQUENCE',
                         7, 'PROCEDURE', 8, 'FUNCTION', 9, 'PACKAGE',
                        11, 'PACKAGE BODY', 12, 'TRIGGER',
                        13, 'TYPE', 14, 'TYPE BODY',
                        19, 'TABLE PARTITION', 20, 'INDEX PARTITION', 21, 'LOB',
                        22, 'LIBRARY', 23, 'DIRECTORY', 24, 'QUEUE',
                        28, 'JAVA SOURCE', 29, 'JAVA CLASS', 30, 'JAVA RESOURCE',
                        32, 'INDEXTYPE', 33, 'OPERATOR',
                        34, 'TABLE SUBPARTITION', 35, 'INDEX SUBPARTITION',
                        39, 'LOB PARTITION', 40, 'LOB SUBPARTITION',
-- Begin Insert following line due to <Bug:1188948>
                        42, 'MATERIALIZED VIEW',
-- End Insert
                        43, 'DIMENSION',
                        44, 'CONTEXT', 47, 'RESOURCE PLAN',
                        48, 'CONSUMER GROUP', 56, 'JAVA DATA',
                            'UNDEFINED'),
         o.ctime, o.mtime,
         to_char(o.stime, 'YYYY-MM-DD:HH24:MI:SS'),
         decode(o.status, 0, 'N/A', 1, 'VALID', 'INVALID'),
         decode(bitand(o.flags, 2), 0, 'N', 2, 'Y', 'N'),
         decode(bitand(o.flags, 4), 0, 'N', 4, 'Y', 'N'),
         decode(bitand(o.flags, 16), 0, 'N', 16, 'Y', 'N')
  from sys.obj$ o, sys.user$ u
  where ...

I hope this helps.

 

[sumsar]

Thanks!!!
Now I just have to persuade my dba to change the view.
/Rasmus