When we discussed Database Link, we learned how to link one database to another and how to access objects in another database.

However, you may sometimes need to transfer and update data in the database to another database.

For example, if you are the manager of a group of pharmacies; Every pharmacy has a database, but every hour it needs to bring all pharmacy accounts from the distributed databases to the main database. This is the task of the Views Materialized; however, the Views Materialized has other tasks that are not detailed here. Here we will use the same example that we used in the Link Database, and let us assume here that the OBAY database is the main database that will receive the data,

Let us assume that we will receive the data in the user TEST,

As for the other database ORCL, which contains the main table, and let us assume that it is EMPLOYEE, owned by the user VBS, whose data we need to transfer and update every Secondly to the main database OBAY.

Of course, we need before anything to make a Database Link between the user TEST in the OBAY database and the user VBS in the ORCL database, for the process of transferring and updating the data of the table

EMPLOYEE from the ORCL database to the OBAY database.

Also, the process of refreshing data between the main table and the Materialized Views is divided into three types:

1- FAST REFRESH: In this type of update, only the data that has changed after the last update is transferred. It does not need to transfer all the data from the table, but only what has changed after the last update.

This type usually shortens the time.

2- COMPLETE REFRESH: This type of update transfers all the table data from the source to the Materialized Views, rewrites the old data and adds the new data.

This type often requires more time than the FAST type.

3- FORCE REFRESH: This type starts first by applying the FAST type if the process fails, for example, if the Logs Views Materialized are not found on the source side,

In this case, the COMPLETE type is applied.

If the refresh type is not specified while creating the Materialized Views, the default is (FORCE).

Materialized Views Logs:-

As we mentioned before, the FAST update type only transfers data that has changed since the last update from the source table to the Materialized Views.

But where is the data that has changed in the source table stored before being transferred to the Mataerialized Views? The answer is stored in the Logs View Materialized, which is a table that is created in the database and even in the user who owns the source table, through the following command:

CREATE MATERIALIZED VIEW LOG ON EMPLOYEE; 

Where EMPLOYEE is the source table. At the moment of writing the above command, the database creates a table with the form

<MLOG$_<TABLE_NAME.

Primary Key Materialized Views:-

But in order to organize the source table, without affecting the FAST REFRESH so that the changes are saved based on the Primary Key, we can, at the moment of creating the Materialized Views, select the WITH PRIMARY KEY option, which is the default (DEFAULT) if no other option is specified, and the other option is ROWID.

When creating Materialized Views with the Primary Key option, the source table must contain a primary Key Constraint. Also, when selecting the Fast Refresh option when creating

Materialized Views, we must have created the Materialized View Logs in the user

that contains the source table, otherwise an error message will appear. The previous explanation may be a bit complicated, but don't worry, focus with me on the scenario that we will implement together step by step, starting from creating users to creating Materialized Views, and that's the gist of the story.

The first database is called OBAY, we will create a user in it called MAIN; We will create Materialized Views in this user to retrieve data from the EMPLOYEE table owned by the SUB user in the ORCL database.

Here are the steps:-

1- We will create the MAIN user in the OBAY database and grant it sufficient permissions.

CREATE USER MAIN IDETIFIED BY MAIN;

GRANT CONNECT,RESOURCE,CREATE DATABASE LINK,CREATE MATERIALIZED VIEW TO MAIN; 

2- On the other side, we make sure of the source table, which is here EMPLOYEE, and also make sure that it contains PRIMARY KEY CONSTRAINT so that we can create MATERIALIZED VIEWS in the other database using the WITH PRIMARY KEY option.

SELECT * FROM EMPLOYEE;

SELECT OWNER,CONSTRAINT_NAME,CONSTRAINT_TYPE,TABLE_NAME FROM USER CONSTRAINTS; 

3- Now in the OBAY database we create a DATABASE LINK between the MAIN user and the SUB user in the ORCL database.

CREATE DATABASE LINK MAINSUB CONNECT TO SUB IDENTIFIED BY SUB USING 'ORCL'; 

4- In the ORCL database and in the SUB user that contains the source table EMPLOYEE, we create a MATERIALIZED VIEW LOG.

CREATE MATERIALIZED VIEW LOG ON EMPLOYEE; 

We have created a MATERIALIZED VIEW LOG for the EMPLOYEE table. The creation of the MATERIALIZED VIEW LOG for the EMPLOYEE table can be verified by the following query:

SELECT * FROM TAB WHERE TNAME LIKE '%EMPLOYEE'; 

5- In the OBAY database, we create the MATERIALIZED VIEW.

CREATE MATERIALIZED VIEW EMPLOYEE_MV REFRESH FAST

START WITH SYSDATE

NEXT SYSDATE + 1/(24*60*60)

WITH PRIMARY KEY

AS SELECT * FROM EMPLOYEE@MAINSUB;

We created the MATERIALIZED VIEW using the FAST REFRESH option because we created the MATERIALIZED VIEW LOG on the other side, otherwise an error message would have appeared,

We also used the WITH PRIMARY KEY option because the source table contains PRIMARY KEY CONSTRAINT, however there is another option which is WITH ROWID.

6- Now we can query the source table and the MATERIALIZED VIEW. You will notice that every second as we specified

SYSDATE + 1/(24*60*60)

The result will be identical between the source table EMPLOYEE and the MATERIALIZED VIEW which is EMPLOYEE_MV.

7- Now if we add a new field in the source table and then repeat the query operations, the new field will appear in the MATERIALIZED VIEW.

If We have made a query for the table MLOG$_EMPLOYEE before the REFRESH operation. We will find information about the fields that were added after the last REFRESH operation. However, after the REFRESH operation, the previous information about the table disappears while waiting for the new one.

The database administrator can make a query about the MATERIALIZED VIEWS that work in the

database via the DBA_MVIEWS table.

We can get information about Another from the same table.

We can get information about JOBS through the DBA_JOBS table.

Of course, the MAIN user can delete the MATERIALIZED VIEW.

DROP MATERIALIZED VIEW EMPLOYEE_MV;