Use db_link to create a materialized view and synchronize data to a data warehouse

Number of materialized views synchronized 1. The purpose is to synchronize some data in the production database to the Data Warehouse. Considering the cost and convenience, the method of regularly refreshing the Materialized View

Number of materialized views synchronized 1. The purpose is to synchronize some data in the production database to the Data Warehouse. Considering the cost and convenience, the method of regularly refreshing the Materialized View

Materialized View synchronization count 1. Usage

Now we need to synchronize some data in the production database to the Data Warehouse. Considering the cost and convenience, we need to regularly refresh the materialized view for data synchronization.

2. Overall Implementation ideas

L create a dblink to access the remote database.

L create a materialized view locally to store the remote data table. When the remote data table changes, it is regularly refreshed to the materialized view.

L create an Oracle job and regularly refresh the table

3. The tables to be synchronized are as follows:

Serial number

Chinese Table Name

Table Name

Description

1

Order table

OMORDER

Order master table, save basic order information

2

Order List

ORDERDETAIL

Store the product information in the order

4.1.4. configuration steps

4.1 configure tns connections in the data warehouse

Testrac =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP) (HOST = 192.168.100.122) (PORT = 1521 ))

(ADDRESS = (PROTOCOL = TCP) (HOST = 192.168.100.123) (PORT = 1521 ))

(LOAD_BALANCE = yes)

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = jscn)

)

)

Test:

[Oracle @ testrac1 goldengate] $ sqlplus sys/sys @ testrac as sysdba

SQL> select count (*) from SYS. GV _ $ INSTANCE

4.2 create a dblink

Syntax:

Create public database link dblink_nameconnect to username identified by passwd using 'tns _ name'

Note:

Dblink_name: The name of dblink.

Username: a remote user name.

Passwd: a remote secret

'Tns _ name': the name of the connection string in tnsnames. ora.

Example:

SQL> create public database link testrac connect to jscn identified by jscn using 'testrac ';

SQL> select count (*) from SYS. GV _ $ INSTANCE @ testrac;

COUNT (*)

----------

2

4.3 create a materialized view to synchronize data

4.3.1 run the following command on the source user to create a materialized view log

Note:

For tables with primary keys defined, execute the following SQL:

Create materialized view log on table name withprimary key;

For tables with no primary key defined, execute the following SQL:

Creatematerialized view log on table name with rowid;

Use SQL statements:

Select 'creatematerialized view log on' | table_name | 'withprimary key; 'fromuser_tables;

Create materialized view log on thh with primary key;

-- These two do not have primary keys.

Create materialized view log on USER_ORDER_INTE with rowid;

4.3.2 execute the following SQL statement on the target user to create a Materialized View

The syntax for creating a materialized view is as follows:

Create materialized view [view_name]

Refresh [fast | complete | force] with [primaryid | rowid]

[

On [commit | demand] |

Start with (start_time) next (next_time)

]

As

{Query statements for creating materialized views}

Use SQL statements:

Select 'create materializedview' | table_name | 'refreshfast on demand as select * fromthh @ testrac; 'fromuser_tables;

Create materialized view CONSIGNEE_ADDRESS refresh fast on demand as select * from thh @ testrac;

Create materialized view CUSTOMER refresh fast on demand as select * from thh @ testrac;

4.3.3 create a scheduled task

Scheduled tasks are executed at every day.

BEGIN

DBMS_MVIEW.refresh (

LIST => 'omorder, ORDERDETAIL, ORDERTRACER, THH, THH_SUB, VACCOUNTREASON, CONSIGNEE_ADDRESS, LOGISTICS_COMPANY, PRODUCT, region, PRODUCTCATEGORY, STORE, STORE_INFO, CUSTOMER, CUSTOMER_INFO, region, GIFT_CARDS_LOG, PROMOTIONS_OFFERS, JOIN_PROMOTIONS, TEL_EMAIL_RECORD, dealcategory, YDORDER, YDORDERTRACER, PRODUCT_BRAND, THH_LOGS ',

METHOD => 'F ',

PARALLELISM => 1 );

END;

For more information about Oracle, see the Oracle topic page? Tid = 12