everythingOracle.bizhat.com

 

'Everything you wanted
to know about Oracle'

Training References Syntax Tell A Friend Contact Us

 

 

SQL

01 02 03 04 05 06 07 08 09 10 11 12 13 14 15 16 17 18 19
<< Previous

Chapter # 17

Next >>


 

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?

     Reviews and Templates for FrontPage
     

Copyright everythingOracle.bizhat.com 2006 All Rights Reserved.