"Just
as there is no loss of basic energy in the universe, so no
thought or action is without its effects, present or ultimate,
seen or unseen, felt or unfelt."
-Norman
Cousins (1912-1990)
|
How
to replicate an object from location "A" to "B"
based on a table with a column object?
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 column object.
An
Oracle user object type is a user-defined datatype that make it
possible to model a complex entities relationship to a simple entity
called an object, in the database. In Oracle9i a user-defined data
type can be replicated between two separated sites (Master Site and
Materialized View Site).
Oracle9i
enhancements to Materialized View replication include support for:
-
Materialized Views (MV) with column objects.
-
Object materialized views.
-
Support for collection columns.
-
Support for REF columns.
To
Support these features, the materialized view log has been enhanced
and can be created for an object table. The object identifier must be
logged by specifying the WITH OBJECT ID clause and if the object
identifier is primary key based the primary key can also be logged.
In
this article, we will learn how to create a materialized view based on
a table with a column object.
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.
CREATE
OR REPLACE TYPE Address_Book_type AS OBJECT
(
Street VARCHAR2(80),
City
VARCHAR2(80),
State
VARCHAR2(2),
Zip
VARCHAR2(10));
/
Step
3: Create the “ADDRESS_BOOK” table with created
“ADDRESS_BOOK_TYPE” object.
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_type );
Step
4: Now, create a Materialized View Log.
CREATE
MATERIALIZED VIEW LOG
ON
address_book 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_type('1576
Dunterry place', 'Orlando','FL','22101'));
INSERT
INTO address_book
VALUES
('200','Dana','Kaz’,
Address_Book_type(‘1299
King Ave.','McLean','VA','43200'));
COMMIT;
Query
the address book table.
SELECT
id, first_name, last_name, c.address_obj.*
FROM
address_book c;
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 = 'ADDRESS_BOOK_TYPE';
OWNER
TYPE_OID
Step
7: Connect to SITEB (Materialized View Site) as the REPLICATOR user.
CONNECT
replicator/...@SITEB
Step
8: Assuming that the “ADDRESS_BOOK_TYPE” object ID is
‘XXXXXXXXXXXXXXXXXXXXX,’
create the “ADDRESS_BOOK_TYPE” object with the same above object
ID.
CREATE
OR REPLACE TYPE Address_Book_type OID ‘XXXXXXXXXXXXXXXXXXXXX’
AS
OBJECT
(
Street VARCHAR2(80),
City
VARCHAR2(80),
State
VARCHAR2(2),
Zip
VARCHAR2(10));
/
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).
CREATE
MATERIALIZED VIEW address_book_mv
REFRESH
FAST AS
SELECT
id, first_name, c.address_obj.*
FROM
replicator.address_book@SITEA c;
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’);
|