Materialized Views with Oracle 8 ?

[Juan Guillermo Fern]

Hi, everyone.

Here at work we have 2 instances of Oracle Databases working: one 8.1.7.4.1 and one 9.2.0.7.0.

Using the same (latest) version of PL/SQL Developer (7.1.2.1362), I can view and edit materialized views on the 9i instance, but not on the 8 version. There, I see as if no materialized view exist. They appear just as simple tables, without access to the DDL code used to create them.

Is this a bug? a setting issue? Any idea how to approach it?

Thanks in advance!

 

[Marco Kalter]

Hello Dominique,

My guess is that you may be running into Oracle bug 1188948. Oracle supplies the following workaround:

Although there is no patch available for , 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.

 

[Juan Guillermo Fern]

I've found and edited the lines you mentioned, restarted PL/SQL Developer, but to no change: When I double click on the Materialized Views icon, it still shows an "empty folder", like if no materialized view has been defined.

Or the changes just take effect on new materialized views? the ones I'll create from now on?

Thanks again.. and by the way, as much as I find Dominique a nice name, I think I'll stay with mine

 

[Marco Kalter]

The Dominique name is a copy & paste error .

Anyway, Juan Guillermo Fern

 

[Juan Guillermo Fern]

Querying both views I got the same behavoir: a Materialized View is regitered twice: as a valid table and as an invalid undefined object.

Now, I've created this mat.views _before_ I made the changes suggested on the first reply. Don't really think that matters, but I mention it, nonetheless.

Thanks.

 

[Marco Kalter]

The problem is that the materialized view is reported as an "undefined object". The fix I mentioned above should fix this! The decode statement in the original view definition returns the default "UNDEFINED" object type, whereas the fix adds "MATERIALIZED VIEW" for object type 42.

Perhaps you can double-check?

 

[Juan Guillermo Fern]

I just double-checked. Restarted my system, thinking that perhaps some services should restart, but to no avail.

This changes should be done clientside, or serverside?

J.G.

 

[Marco Kalter]

The change must be done under the SYS user in the database. You must change the view definition.