iSelfSchooling - Replicating data with a NESTED TABLE collection type

This hand-on 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.

CONNECT …/…@...

STEPS to implement replication:

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

SQL> CONNECT replicator/…@SITEA

CREATE TYPE … AS OBJECT …

Step 2: Create the “address_book_type_object” object. Then create a nested table type from created object.

SQL> CREATE OR REPLACE TYPE address_book_type_object

AS OBJECT (

id_address NUMBER(1),

address VARCHAR2(20));

CREATE TYPE … AS TABLE OF …

SQL> CREATE TYPE address_book_nested_type

AS TABLE OF address_book_type_object;

CREATE TABLE … NESTED TABLE …

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

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

CREATE MATERIALIZED VIEW LOG …

Step 4: Now, create a Materialized View Log.

SQL> CREATE MATERIALIZED VIEW LOG ON address_book;

ALTER MATERIALIZED VIEW LOG ON address_book ADD(address_obj);

SQL> CREATE MATERIALIZED VIEW LOG ON address_nested

WITH PRIMARY KEY;

INSERT INTO … VALUES (object_type(),…)

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

SQL> 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')));

SQL> INSERT INTO address_book

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

address_book_nested_type (

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

SQL> COMMIT;

Query the address book table.

SQL> SELECT *

FROM address_book;

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

SQL> CONNECT system/…@SITEA

Obtaining an Object ID (OID)

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

SQL> SELECT OWNER, TYPE_OID FROM DBA_TYPES

WHERE TYPE_NAME LIKE 'ADDRESS%';

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

SQL> CONNECT replicator/...@SITEB

Step 9: Assuming that your object IDs are

‘XXXXXXXXXXXXXXXXXXXXX’ and ‘YYYYYYYYYYYYYYYYYYYYY.

SQL> CREATE OR REPLACE TYPE address_book_type_object

OID ‘XXXXXXXXXXXXXXXXXXXXX’

AS OBJECT (

id_address NUMBER(1),

address VARCHAR2(20));

Creating type using OID

SQL> CREATE TYPE address_book_nested_type

OID ‘YYYYYYYYYYYYYYYYYYYYY’

AS TABLE OF address_book_type_object;

CREATE MATERIALIZED VIEW …FROM ..@...

Step 10: 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.

SQL> CREATE MATERIALIZED VIEW address_book_mv

NESTED TABLE address_obj STORE AS address_nested_mv

REFRESH FAST AS

SELECT *

FROM replicator.address_book@SITEA;

DBMS_MVIEW.REFRESH procedure

Step 11: 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.

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

A great secret of success is to go through life as a man who never gets used up. Albert Schweitzer (1875 - 1965)

Questions:

Q: What is a data replica?

Q: What is the difference between a materialized view and a materialized view log?

Q: What is an object ID?

Q: How do you retrieve an object ID?

Q: How do you use an object ID to create an object type?