LOB Problem
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: LOB Problem

  1. #1
    Join Date
    May 2003
    Posts
    32

    LOB Problem

    Hi .
    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
    BUILD DEFERRED
    REFRESH FORCE
    for update
    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.


    Suggesstions.

    RAFF

  2. #2
    Join Date
    Jan 2001
    Posts
    3,131
    Can you just copy the table and convert it to a MV?

    ON PREBUILT TABLE

    MH
    I remember when this place was cool.

  3. #3
    Join Date
    Jan 2001
    Posts
    3,131
    I found this too

    http://download-west.oracle.com/docs...0/mv.htm#36835


    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.


    MH
    I remember when this place was cool.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width