user3797729
user3797729

Reputation: 87

How to create materialized view log on the remote database

Here our issue is we have to create materialized view logs on the remote database's base tables

for respective materialized views on the local database

Both local and remote databases are Oracle 11g (11.2)

example: s1 and s2 are two schemas

s1 schema is on the local databse s2 schema is on the remote databae

we have to create materialized view logs on the s2 schema(Remote database)

we created database link for accessing the s2 schema's objects(Remote database)

But we are getting this error ORA-00949 'illegal reference to remote database'

But in s1 schema(local database) we are able to creating the materialized views

Thanks

Ragav

Upvotes: 1

Views: 6407

Answers (1)

golosovsky
golosovsky

Reputation: 718

There's supposed to be no problem with creating Mviews which perform Fast Refresh (refreshing an mview using mview log on target table) querying tables which are located on a remote site.

What you need to do:

  1. Create a database link between the two Oracle databases
  2. Perform a testing that the db link actually work (run a few queries on the "target table")
  3. On the remote site — create mview log on the target table
  4. Create mview on the local database with Fast Refresh option (with for example - refresh on demand) and query the target table in the mview's SQL (for example select name, date from target_schema.target_table@remote_oracle_db;)
  5. Now, run a complete refresh on your mview: dbms_snapshot.refresh('local_schema.mview_x','c');
  6. From now on, you can refresh the mview using the Fast Refresh option: dbms_snapshot.refresh('local_schema.mview_x','f');

Upvotes: 3

Related Questions