CREATE TABLE REPLICATION USING MATERIALIZED VIEWS ORACLE

CREATE TABLE REPLICATION USING MATERIALIZED VIEWS
————————————————-
SOURCE TABLE : LOG_RECORD
TARGET TABLE : LOG_RECORD_UPD

We have a large table called Log_Record with well over 50 million rows and we will create a replication for
this table to a remote database.
1. Create the Database Link in the Remote Database to Access Source Database.

create public database link  tosource connect to schemaname identified by ****** using ‘(DESCRIPTION =
(ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = xxxxxx)(PORT = 1521)) ) (CONNECT_DATA =
(SERVICE_NAME = xxxx) ) )’ ;

 
2. Create Materialized view log in Source Database
create materialized view log on log_record with rowid, sequence;

 
3. Create MV in Remote Database using CMVAS( Create Materialized View As )

CREATE materialized VIEW LOG_RECORD_UPD REFRESH FAST START WITH SYSDATE NEXT SYSDATE+1/1440 WITH ROWID FOR
UPDATE as select * from log_record@SWXTEST ;
— The Table will be refreshed every one minute via a Job
— The MV we created is a Read – Write MV

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s