Error while creating of Snapshot

Tagged: 

This topic contains 2 replies, has 0 voices, and was last updated by  monash_aus 11 years ago.

  • Author
    Posts
  • #1870

    monash_aus
    Participant

    Hi,

    I am trying to create a snapshot in a different schema to that of base table.I have required privileges to create a snapshot on the base tables. The following error occurs when I try to create snapshot with the below syntax.

    Error:


    ORA-12018: following error encountered during code generation for “SWCWQTOOL”.”RT_DISTSYS”

    ORA-00942: table or view does not exist

    Syntax


    CREATE SNAPSHOT snapshot1

    REFRESH FAST START WITH SYSDATE NEXT SYSDATE+1/24

    AS

    SELECT * FROM table1.

    But I can query the table1 and view the data.

  • #2015

    Amit Bansal
    Keymaster

    Hi,

    I have not encountered this error earlier. Did you try searching metalink.

    Also what happens when you use schema name to identify the table. e.g

    CREATE SNAPSHOT snapshot1

    REFRESH FAST START WITH SYSDATE NEXT SYSDATE+1/24

    AS

    select * from scott.table1;

    Cheers

    Amit

  • #2016

    Sasi
    Participant

    Monash_aus,

    << trying to create a snapshot in a different schema to that of base >>

    thats an interesting one.. creating a MV/snapshot in a different schema to that of base schema.

    Snapshots were used in 8i. From 9i we should use materialized views (MV) . To create a fast refresh MV we need to create a MV log first in the source schema and give access to this MV log to the target schema.

    In case if you are not familiar with Materialized View (MV) , refer the below steps to create a MV that will get refreshed once an hour.


    Start


    Lets say you have two schemas , SAM and JOHN .

    Now ,SAM wants to create a materialized view (TAB1_MV) on JOHN.TAB1 table,

    1st SAM should have the following privileges granted to him

    1.As sysdba grant the following privileges to SAM.

    grant create materialized view to SAM;

    grant global query rewrite to SAM;

    grant on commit refresh to SAM;

    2.Login as user JOHN and grant the required table access to SAM,

    grant select on JOHN.TAB1 to SAM;

    create materialized view log on JOHN.TAB1 with primary key;

    The above sql will create a MV log named MLOG$_TAB1 in JOHN schema.

    grant select on JOHN.MLOG$_TAB1 to SAM;

    3.Now connect to SAM schema and run the follwoing SQLs,

    create materialized view TAB1_MV

    build immediate

    refresh fast

    start with sysdate

    next sysdate+1/24

    with primary key

    enable query rewrite

    as

    SELECT * from TAB1;

    This creates the required MV and populates the data and refresh takes ple every hour.

    Hope this helps.


    Note 1:

    To check if SAM has appropriate privileges to create a materialized view run the following,

    select grantee,privilege from user_sys_privs ;

    create materialized

    global query rewrite

    on commit refresh

    should see an output something like above

    Note 2:

    If the materialized view log name is more than 30 characters then the name would be trunacted automatically while creating MV log. This will give an error as table not found while creating MV.


    End


    For more info on MV refer, (9i)

    http://download.oracle.com/docs/cd/B10501_01/server.920/a96567/repmview.htm

You must be logged in to reply to this topic.