Entire Process of creating Oracle Materialized View

If we encounter the problem that we need to fetch data from other system databases for statistical analysis, we may choose to use ORACLE's ODI tool for data extraction, but the database users provided by the other party do not have any objects, you only have the permission to query all tables, so you cannot reverse the data.

Therefore, we decided to use the materialized view to retrieve the data in the database of the other party. Although the data volume is large, it is only taken once a month. If it is set to incremental update, it will not be too slow. Now, the creation process of the materialized view is recorded as an example ).

I. Prerequisites and remarks

Assume that both databases are ORACLE10g. The table name to be synchronized is GG_ZLX_ZHU. the user name of the other database is username, And the password is password and SID is CPEES.

2. Start to work

1. Create DB_LINK first

 
 

  
  
CREATE DATABASE LINK to_cpees  
  
  
 
  
  
CONNECT TO "username" identified by "password"  
  
  
 
  
  
using "CPEES"  
 
 

CPEES is the new TNS to the peer database. Run the command. Now we have created DB_LINK TO_CPEES to the other database.

2. Create an Oracle Materialized View to quickly refresh logs

As mentioned above, the view refresh method will be incremental in the future. Therefore, in combination with incremental refresh, ORACLE requires the creation of Materialized View logs on the table.

 
 

  
  
CREATE MATERIALIZED VIEW LOG ON GG_ZLX_ZHU  
  
  
 
  
  
WITH PRIMARY KEY  
  
  
 
  
  
INCLUDING NEW VALUES;  
  
  
 
 
 

(The preceding SQL statement must be executed in a remote database and cannot be executed locally)

3. Create an Oracle Materialized View

The Oracle materialized view is based on the name. It should belong to the view, but it is materialized. Its materialized view is intended for general views without real physical storage. In fact, you can simply regard the materialized view as a physical table and do not explain it in detail ).

 
 

  
  
Create materialized view GG_ZLX_ZHU -- CREATE a MATERIALIZED VIEW
  
  
 
  
  
Build immediate -- created after the view is compiled
  
  
 
  
  
Refresh fast with primary key -- refresh fast and incremental based on the primary key of the master table)
  
  
 
  
  
On demand -- refresh
  
  
 
  
  
Enable query rewrite -- read/write
  
  
 
  
  
AS
  
  
 
  
  
SELECT * FROM GG_ZLX_ZHU @ TO_CPEES; -- query statement
 
 

4. View refresh

Based on business needs, the data is refreshed from time to time every month. Therefore, it cannot be a JOB with a large number of tasks, and therefore cannot be refreshed one by one. Based on the preceding conditions, use the REFRESH method in the DBMS_MVIEW toolkit of the ORACLE tool to REFRESH the materialized view. This method has two parameters. The first parameter is the name of the materialized view to be refreshed, and the second parameter is the refresh method. We can write and store the data, call the REFRESH method once for each materialized view, or use "," to REFRESH the materialized view since it is 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;  
  
  
 
 
 

Note:

1. If you need to refresh multiple materialized views at the same time, you must use commas to connect the names of materialized views.

Each view must specify the refresh method f, incremental refresh, and c. Completely refresh ,? , Force refresh ).

2. After the log and materialized view are created on the current day, delete the log and recreate the materialized view. Otherwise, the incremental view cannot be created.

Refresh.

3. Because the materialized views written above are updated based on the primary key, the primary table must have a primary key.

4. In the above Article, the red color is replaceable. You can modify it based on your project requirements.

I hope the above content will help you.

Forget to delete the log and materialized view. Delete the log and materialized view separately.

 
 

  
  
DROP MATERIALIZED VIEW LOG ON GG_ZLX_ZHU@TOCPEES;  
  
  
 
  
  
DROP MATERIALIZED VIEW GG_ZLX_ZHU;  
  
  
 
 
 

ORACLE instance creation process

Statement syntax for oracle time addition and subtraction

Oracle deadlock handling

Statement of Oracle paging Query

Oracle condition branch statement example