iSelfSchooling.com  Since 1999     References  |  Search more  | Oracle Syntax  | Free Online Oracle Problem Solver

    .Home      .Subscribe     .Login      .Start Learning      .Certification      .Cancel      .Password Reminder      .Password Change     .Sign out

 

. Online Accounting        .Copyright & User Agreement   |
    .Vision      .Biography     .Acknowledgement

.Contact Us      .Comments/Suggestions       .Email2aFriend    |

 

Online Oracle Training for beginners and advanced - The most comprehensive Oracle tutorial

The authors do not guarantee or take any responsibility for the accuracy, or completeness of the information.

Basics - SQL 

 

Lesson 01 | Lesson 02 | Lesson 03 | Lesson 04 | Lesson 05 | Lesson 06 | Lesson 07 | Lesson 08 | Lesson 09 | Lesson 10 | Lesson 11 | Lesson 12 | Lesson 13 | Lesson 14 | Lesson 15 | Lesson 16 | Lesson 17 | Lesson 18 |

 

Lesson 17

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?