|
Try not to become a
man of success but rather to become a man of value. Albert
Einstein (1879 - 1955) |
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;
SQL> 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?
|