How to replicate data from location "A" to "B" based on a table with a NESTED TABLE collection type?

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 a table with a NESTED TABLE collection type.

We will learn how to create a materialized view based on a table with a NESTED TABLE collection type.

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” object. Then create a nested table type from created object.

CREATE OR REPLACE TYPE address_book_type_object AS OBJECT (

id_address NUMBER(1),

address VARCHAR2(20));

CREATE TYPE address_book_nested_type AS TABLE OF address_book_type_object;

Step 3: Create the “ADDRESS_BOOK” table with created “ADDRESS_BOOK_NESTED_TYPE” type.

CREATE TABLE address_book

( id NUMBER(10) CONSTRAINT address_book_pk PRIMARY KEY,

first_name VARCHAR2(15),

last_name VARCHAR2(15),

address_obj address_book_nested_type)

NESTED TABLE address_obj STORE AS address_nested

((PRIMARY KEY (NESTED_TABLE_ID, id_address)));

Step 4: Now, create a Materialized View Log.

CREATE MATERIALIZED VIEW LOG ON address_book;

ALTER MATERIALIZED VIEW LOG ON address_book ADD(address_obj);

CREATE MATERIALIZED VIEW LOG ON address_nested WITH PRIMARY KEY;

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

INSERT INTO address_book

VALUES ('100','Borna','Kaz',

address_book_nested_type(

address_book_type_object (1,‘Company Name’),

address_book_type_object (2,'1576 Dunterry place')

address_book_type_object (3, 'Orlando','FL, 22101')));

INSERT INTO address_book

VALUES ('200','Dana','Kaz’,

address_book_nested_type (

address_book_type_object (1,‘Company Name2’')));

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 LIKE 'ADDRESS%';

OWNER TYPE_OID

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

CONNECT replicator/...@SITEB

Step 8: Assuming that your object IDs are

‘XXXXXXXXXXXXXXXXXXXXX’ and ‘YYYYYYYYYYYYYYYYYYYYY.

CREATE OR REPLACE TYPE address_book_type_object

OID ‘XXXXXXXXXXXXXXXXXXXXX’

AS OBJECT (

id_address NUMBER(1),

address VARCHAR2(20));

CREATE TYPE address_book_nested_type

OID ‘YYYYYYYYYYYYYYYYYYYYY’

AS TABLE OF address_book_type_object;

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) by executing refresh procedure.

CREATE MATERIALIZED VIEW address_book_mv

NESTED TABLE address_obj STORE AS address_nested_mv

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’);