Article: 

How to replicate data from location "A" to "B" based on an object table?

Gathered By: John Kazerooni

Today, a lot of company wants to replicate their data for a reason of backup and recovery, accessibility, performance, etc.

This article is intended to introduce the enhancement in Oracle9i to teach how to replicate an object from location “A” to “B” based on an object table.

We will learn how to create a materialized view based on a table with a table object.

We assume that you have two sites (Master Site with a service name of SITEA and Materialized View Site with a service name of SITEB). Also we assume that we have user schema called REPLICATOR in both sites.

STEPS to implement replication:

Step 1: Connect to “SITEA” (Master Site) as the REPLICATOR user.

CONNECT replicator/…@SITEA

Step 2: Create the “Address_Book_type” object.

CREATE OR REPLACE TYPE Address_Book_type AS OBJECT

( id NUMBER(10),

first_name VARCHAR2(15),

last_name VARCHAR2(15),

street VARCHAR2(80),

city VARCHAR2(80),

state VARCHAR2(2),

zip VARCHAR2(10));

/

Step 3: Create the “ADDRESS_BOOK” table with created “ADDRESS_BOOK_TYPE” object.

CREATE TABLE address_book OF address_book_type

(id PRIMARY KEY) OBJECT ID PRIMARY KEY;

Step 4: Now, create a Materialized View Log.

CREATE MATERIALIZED VIEW LOG

ON address_book WITH OBJECT ID, PRIMARY KEY;

Step 5: Insert some records into your address book table and then commit the transaction.

INSERT INTO address_book

VALUES (Address_Book_type('100','Borna','Kaz', '1576 Dunterry place', 'Orlando','FL','22101'));

INSERT INTO address_book

VALUES (Address_Book_type('200','Dana','Kaz’,‘1299 King Ave.','McLean','VA','43200'));

COMMIT;

Query the address book table.

SELECT *

FROM address_book;

Step 5: Connect to SITEA again with a DBA privilege.

CONNECT system/…@SITEA

Step 6: Obtain Object ID of created “ADDRESS_BOOK_TYPE” object.

SELECT OWNER, TYPE_OID FROM DBA_TYPES

WHERE TYPE_NAME = 'ADDRESS_BOOK_TYPE';

OWNER TYPE_OID

Step 7: Connect to SITEB (Materialized View Site) as the REPLICATOR user.

CONNECT replicator/...@SITEB

Step 8: Assuming that the “ADDRESS_BOOK_TYPE” object ID is

‘XXXXXXXXXXXXXXXXXXXXX,’ create the “ADDRESS_BOOK_TYPE” object with the same above object ID.

CREATE OR REPLACE TYPE Address_Book_type OID ‘XXXXXXXXXXXXXXXXXXXXX’

AS OBJECT

( id NUMBER(10),

first_name VARCHAR2(15),

last_name VARCHAR2(15),

street VARCHAR2(80),

city VARCHAR2(80),

state VARCHAR2(2),

zip VARCHAR2(10));

/

Step 9: Now, create a Materialized View that it will be updated as soon as there is any changes on the Master table (ADDRESS_BOOK).

CREATE MATERIALIZED VIEW address_book_mv OF address_book_type

REFRESH FAST AS

SELECT * FROM replicator.address_book@SITEA;

Step 10: From now on, any changes in the ADDRESS_BOOK table in the Master Site (SITEA) will be replicated to the SITEB (Materialized View Site) by executing DBMS_MVIEW.REFRESH procedure on the SITEB.

EXECUTE dbms_mview.refresh(‘address_book_mv’,’F’);