AskDBA.org Forum Forum for Oracle DBA's

About This Topic

• Posted 3 years ago
• By monash_aus
• Latest reply from Sasi
• This topic is not a support question
• RSS feed for this topic

Tags

• Snapshot

1. 

   monash_aus
   

   new member
   Joined: Aug '08
   Posts: 1

   offline

   Reputation:2

    

   
   Posted 3 years ago
   #

   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.
2. 

   Amit Bansal
   

   Oracle DBA
   Joined: Jun '08
   Posts: 73

   offline

   Reputation:96

    

   
   Posted 3 years ago
   #

   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

   Cheers
   Amit
   http://askdba.org/weblog/
3. 

   Sasi
   

   Senior Oracle DBA
   Joined: Aug '08
   Posts: 1

   offline

   Reputation:1

    

   
   Posted 3 years ago
   #

   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