Create an instance in the Oracle Materialized View

In our project, we encountered the problem of getting data from the databases of other systems for statistical analysis. We initially chose to use ORACLE's ODI tool for data extraction, but the database provided by the other party

In our project, we encountered the problem of getting data from the databases of other systems for statistical analysis. We initially chose to use ORACLE's ODI tool for data extraction, but the database provided by the other party

In the company's project, if you need to retrieve data from the databases of other systems for statistical analysis, you should use Oracle's ODI tool for data extraction, however, the database users provided by the other party do not have any objects, but only have the permission to query all tables. Therefore, data inversion cannot be performed. 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 record the creation process of the materialized view (take a table 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 a 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 a Materialized View

Materialized View, which is based on the name, should belong to the view, but is materialized. Its materialized view is intended for general views that do not have real physical storage. In fact, materialized views can be simply viewed as a physical table (no more specific explanation ).

Create materialized view GG_ZLX_ZHU -- CREATE a MATERIALIZED VIEW

Build immediate -- created after the view is compiled

Refresh fast with primary key -- incrementally REFRESH (FAST, 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

BEGIN

DBMS_MVIEW.REFRESH ('gg _ ZLX_ZHU ', 'F ');

END P_MVIEW_REFRESH;

Or use

Create or replace procdure P_MVIEW_REFRESH

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, c, full 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;