[Sasi]

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

[Author]

Posts