I want to create a updatable materialized view with the DEFERRED option. In other words just want to create a mv and dont want the DATA of the base table to be filled in at creation of this mv.
It returns me back with the following error.
"ORA-22992: cannot use LOB locators selected from remote tables"
Here is my code
CREATE MATERIALIZED VIEW xyz_mv
AS SELECT * FROM xyz@ORACLE.Net
My xyz table contains a column of the LOB data type.
I m working on Oracle 9i release 2.
Its working fine if i remove the DEFERRED keyword, and will create an updatable mv with the LOB column. But i have to use this DEFERRED keyword as i m working on a WAN environment and dont want to put the load in it while creating the MV's.
How am i going to create an updatable mv base on the table containing LOB columns.
Can you just copy the table and convert it to a MV?
ON PREBUILT TABLE
I found this too
Materialized View Restrictions
You should keep in mind the following restrictions:
The defining query of the materialized view cannot contain any non-repeatable expressions (ROWNUM, SYSDATE, non-repeatable PL/SQL functions, and so on).
The query cannot contain any references to RAW or LONG RAW datatypes or object REFs.
If the defining query of the materialized view contains set operators (UNION, MINUS, and so on), rewrite will use them for full text match rewrite only.
If the materialized view was registered as PREBUILT, the precision of the columns must agree with the precision of the corresponding SELECT expressions unless overridden by the WITH REDUCED PRECISION clause.
If the materialized view contains the same table more than once, it is possible to do a general rewrite, provided the query has the same aliases for the duplicate tables as the materialized view.