Oracle materialized View Creation instance

The company's project encountered the need to take data from other systems in the database for statistical analysis of the problem, the initial choice of using Oracle's ODI tool to extract the number, but the other side provided by the database users do not have any objects, but only have the right to query all tables, so can not do data reverse. Then decided to use the materialized view, the data in the other database, although the amount of data is large, but only once a month, and if set to incremental update, it will not be too slow. Now record the creation process of the materialized view (take one table for example).

I. Preparation conditions and Notes

If both sides of the database are oracle10g, need to sync up the table called: Gg_zlx_zhu, the other Side database username: username, password: password,sid:cpees.

Second, get to work.

1, first to create Db_link

CREATE DATABASE LINK To_cpees

CONNECT to "username" identified by "password"

Using "Cpees"

Where Cpees is the newly created TNS to the other database. Execute, and now we have created a db_link to_cpees to the other database.

2. Create materialized view Quick Refresh Log

Because it says, the refresh of the view will take the form of incremental refresh, so in order to keep up with incremental refreshes, Oracle requires that the materialized view log be established on the live table.

CREATE materialized VIEW LOG on Gg_zlx_zhu
With PRIMARY KEY
including NEW VALUES;

(The above SQL is to be executed on the remote database and cannot be executed locally)

3. Create materialized views

Materialized views, from the top of the name, should be part of the view, but it does materialize. Its materialization is for normal view and there is no real physical storage, in fact, can be simply a materialized view as a physical table (no longer a specific explanation).

Create materialized view Gg_zlx_zhu--creating materialized views
Build IMMEDIATE--created after the view is written
Refresh fast with PRIMARY key--Refresh (FAST, increment) based on primary table primary key
On DEMAND-Refresh by user when required
Enable QUERY REWRITE--Writable
As
SELECT * from Gg_zlx_zhu@to_cpees; --Query statement

4, view Refresh

According to business needs, the monthly refresh, so can not be a job, and a large number, so it can not be a refresh. Based on the above conditions, choose to refresh the materialized view with the Oracle Self-tool Dbms_mview tool kit. The method has two parameters, the first parameter is the materialized view name that needs to be refreshed, and the second parameter is the Refresh method. We can write and store it, call the Refresh method once for each materialized view, or use "," to make a refresh of the materialized view since it was connected. As follows:

CREATE OR REPLACE procdure P_mview_refresh as

BEGIN

Dbms_mview. REFRESH (' Gg_zlx_zhu ', ' f ');

End P_mview_refresh;

or use

CREATE OR REPLACE procdure P_mview_refresh as

BEGIN

Dbms_mview. REFRESH (' Gg_zlx_zhu,gg_zlx_fu ', ' FF ');

End P_mview_refresh;

Attention:

1. If you need to refresh multiple materialized views at the same time, you must connect each materialized view name with commas and

Each view will indicate the refresh mode (f, incremental refresh, C, full refresh,?, Force refresh).

2, when the log and materialized views are created, delete the logs, you will need to re-create the materialized view, otherwise you cannot increment

Refresh.

3. Because the materialized view written above is updated according to the primary key, the primary table must have a primary key.

4, the above article red is replaceable, we can according to their own project needs to modify.

I hope the above content will be helpful to everyone.

Forgot to write Delete method, log and materialized view to be deleted separately

DROP materialized VIEW LOG on Gg_zlx_zhu@tocpees;

DROP materialized VIEW Gg_zlx_zhu;