|
Replicating data with a NESTED TABLE
collection type
Hands-On
Introduction
Today, a lot
of company wants to replicate their data for
a reason of backup and recovery,
accessibility, performance, etc.
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�);
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? |
