|
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? |
