Clearing registration information of materialized views

Oracle automatically clears logs in the materialized view. Oracle determines when to delete the materialized view log based on the registration information on the base table of the Materialized View and the refresh of the materialized view.

The registration information of the materialized view is cleared when the materialized view is deleted. However, when the materialized view is created in a remote database, the deletion of registration information may be more complex.

 

If the materialized view is created locally, Oracle has all the data dictionary information. Therefore, all tests in this article are for the remote materialized view.

First, let's look at the example of clearing materialized view logs. This operation is fully performed by Oracle, even if the user does not have the DELETE permission for Materialized View logs.

First, log on to the remote database to create the base table and materialized view logs used for the test:

SQL> CONN TEST/TEST @ TEST2 is connected.
SQL> CREATE TABLE T1 (ID NUMBER PRIMARY KEY );

The table has been created.

SQL> CREATE MATERIALIZED VIEW LOG ON T1;

The materialized view log has been created.

The following describes how to create a database chain and materialized view locally:

SQL> CONN YANGTK/YANGTK is connected.
SQL> CREATE DATABASE LINK TEST2 CONNECT TO TEST IDENTIFIED BY TEST USING 'test2 ';

The database link has been created.

SQL> CREATE MATERIALIZED VIEW MV_TEST REFRESH FAST AS SELECT * FROM T1 @ TEST2;

The materialized view has been created.

Start the test below:

SQL> INSERT INTO T1 @ TEST2 VALUES (1 );

One row has been created.

SQL> COMMIT;

Submitted.

SQL> SELECT COUNT (*) FROM MLOG $ _ T1 @ TEST2;

COUNT (*)
----------
1

SQL> EXEC DBMS_MVIEW.REFRESH ('mv _ test ')

The PL/SQL process is successfully completed.

SQL> SELECT COUNT (*) FROM MLOG $ _ T1 @ TEST2;

COUNT (*)
----------
0

It can be seen that the materialized view log is automatically cleared after the materialized view is refreshed. However, because the database link user TEST has the permission to delete MLOG $ _ T1. Next, create a new user and grant only the query permission for MLOG $ _ T1:

SQL> CONN TEST/TEST @ TEST2 is connected.
SQL> CREATE USER AAA IDENTIFIED BY AAA;

The user has been created.

SQL> GRANT CONNECT, RESOURCE TO AAA;

Authorization successful.

SQL> GRANT SELECT ON T1 TO AAA;

Authorization successful.

SQL> GRANT SELECT ON MLOG $ _ T1 TO AAA;

Authorization successful.

Because my local database's GLOBAL_NAMES is set to FALSE, you can set up another database chain for different users to point to the TEST2 database.

SQL> CONN YANGTK/YANGTK is connected.
SQL> SHOW PARAMETER GLOBAL_NAMES

NAME TYPE VALUE
---------------------------------------------------------------------
Global_names boolean FALSE
SQL> DROP MATERIALIZED VIEW MV_TEST;

The materialized view has been deleted.

SQL> CREATE DATABASE LINK TEST2_NEW CONNECT TO AAA IDENTIFIED BY AAA USING 'test2 ';

The database link has been created.

SQL> SELECT COUNT (*) FROM TEST. T1 @ TEST2_NEW;

COUNT (*)
----------
1

SQL> CREATE MATERIALIZED VIEW MV_TEST2 REFRESH FAST AS SELECT * FROM TEST. T1 @ TEST2_NEW;

The materialized view has been created.

SQL> INSERT INTO T1 @ TEST2 VALUES (2 );

One row has been created.

SQL> SELECT COUNT (*) FROM MLOG $ _ T1 @ TEST2;

COUNT (*)
----------
1

SQL> EXEC DBMS_MVIEW.REFRESH ('mv _ test2 ')

The PL/SQL process is successfully completed.

SQL> SELECT COUNT (*) FROM MLOG $ _ T1 @ TEST2;

COUNT (*)
----------
0

It can be seen that the removal of Materialized View logs is completely Oracle's content operations, and it has nothing to do with the user's permissions.

The deletion of Materialized View logs depends on the registration information of materialized views. How can the registration information of materialized views be cleared:

SQL> SELECT OWNER, NAME, MVIEW_SITE, MVIEW_ID
2 FROM USER_REGISTERED_MVIEWS @ TEST2;

Owner name MVIEW_SITE MVIEW_ID
----------------------------------------------------------------------
YANGTK MV_TEST2 YTK. US. ORACLE. COM 64

SQL> DROP MATERIALIZED VIEW MV_TEST2;

The materialized view has been deleted.

SQL> SELECT OWNER, NAME, MVIEW_SITE, MVIEW_ID
2 FROM USER_REGISTERED_MVIEWS @ TEST2;

Unselected row

It is not difficult to find that Oracle automatically transmits the deleted message to the master site when deleting the materialized view. The registration information of the materialized view is also cleared at the master site.

Is that simple? Yes. However, the premise is that the database chain used to create the materialized view exists and is available.

If you delete this database chain:

SQL> CREATE MATERIALIZED VIEW MV_TEST2 REFRESH FAST AS SELECT * FROM TEST. T1 @ TEST2_NEW;

The materialized view has been created.

SQL> SELECT OWNER, NAME, MVIEW_SITE, MVIEW_ID
2 FROM USER_REGISTERED_MVIEWS @ TEST2;

Owner name MVIEW_SITE MVIEW_ID
----------------------------------------------------------------------
YANGTK MV_TEST2 YTK. US. ORACLE. COM 65

SQL> DROP DATABASE LINK TEST2_NEW;

The database link has been deleted.

SQL> DROP MATERIALIZED VIEW MV_TEST2;

The materialized view has been deleted.

SQL> SELECT OWNER, NAME, MVIEW_SITE, MVIEW_ID
2 FROM USER_REGISTERED_MVIEWS @ TEST2;

Owner name MVIEW_SITE MVIEW_ID
----------------------------------------------------------------------
YANGTK MV_TEST2 YTK. US. ORACLE. COM 65

After deleting the database chain, delete the materialized view. The operation of deleting the materialized view itself does not report an error, but the materialized view log of the main site is not cleared.

In addition, Oracle is not smart enough to use other equivalent database chains. It only uses the database chain specified in the materialized view definition.

Next we will continue to observe the log clearing status of the Materialized View:

SQL> INSERT INTO T1 @ TEST2 VALUES (3 );

One row has been created.

SQL> COMMIT;

Submitted.

SQL> SELECT COUNT (*) FROM MLOG $ _ T1 @ TEST2;

COUNT (*)
----------
1

Materialized views

The following describes how to reconstruct the database chain and the materialized view just deleted:

SQL> CREATE DATABASE LINK TEST2_NEW CONNECT TO AAA IDENTIFIED BY AAA USING 'test2 ';

The database link has been created.

SQL> CREATE MATERIALIZED VIEW MV_TEST2 REFRESH FAST AS SELECT * FROM TEST. T1 @ TEST2_NEW;

The materialized view has been created.

SQL> SELECT OWNER, NAME, MVIEW_SITE, MVIEW_ID
2 FROM USER_REGISTERED_MVIEWS @ TEST2;

Owner name MVIEW_SITE MVIEW_ID
----------------------------------------------------------------------
YANGTK MV_TEST2 YTK. US. ORACLE. COM 66

SQL> SELECT COUNT (*) FROM MLOG $ _ T1 @ TEST2;

COUNT (*)
----------
1

Because the OWNER, NAME, and MVIEW_SITE information are identical, Oracle does not create a new registration information, but updates the registration information. The materialized view ID is changed from 65 to 66.

However, the creation of the materialized view does not clear the logs of the materialized view.

In addition, subsequent refresh operations cannot clear materialized view logs:

SQL> INSERT INTO T1 @ TEST2 VALUES (4 );

One row has been created.

SQL> COMMIT;

Submitted.

SQL> SELECT COUNT (*) FROM MLOG $ _ T1 @ TEST2;

COUNT (*)
----------
2

SQL> EXEC DBMS_MVIEW.REFRESH ('mv _ test2 ')

The PL/SQL process is successfully completed.

SQL> SELECT COUNT (*) FROM MLOG $ _ T1 @ TEST2;

COUNT (*)
----------
2

At this time, the materialized view log cannot be cleared normally. Although the registration information can be removed by deleting the materialized view, the log of the materialized view cannot be cleared.

SQL> DROP MATERIALIZED VIEW MV_TEST2;

The materialized view has been deleted.

SQL> SELECT OWNER, NAME, MVIEW_SITE, MVIEW_ID
2 FROM USER_REGISTERED_MVIEWS @ TEST2;

Unselected row

SQL> CREATE MATERIALIZED VIEW MV_TEST2 REFRESH FAST AS SELECT * FROM TEST. T1 @ TEST2_NEW;

The materialized view has been created.

SQL> INSERT INTO T1 @ TEST2 VALUES (5 );

One row has been created.

SQL> COMMIT;

Submitted.

SQL> SELECT COUNT (*) FROM MLOG $ _ T1 @ TEST2;

COUNT (*)
----------
3

SQL> EXEC DBMS_MVIEW.REFRESH ('mv _ test2 ')

The PL/SQL process is successfully completed.

SQL> SELECT COUNT (*) FROM MLOG $ _ T1 @ TEST2;

COUNT (*)
----------
3

SQL> SELECT OWNER, NAME, MVIEW_SITE, MVIEW_ID
2 FROM USER_REGISTERED_MVIEWS @ TEST2;

Owner name MVIEW_SITE MVIEW_ID
----------------------------------------------------------------------
YANGTK MV_TEST2 YTK. US. ORACLE. COM 67

When querying the USER_BASE_TABLE_MVIEWS view, you can find that the message with MVIEW_ID 65 is not cleared.

SQL> SELECT OWNER, MASTER, MVIEW_ID FROM USER_BASE_TABLE_MVIEWS @ TEST2;

Owner master MVIEW_ID
--------------------------------------------------
TEST T1 65
TEST T1 67

Now the problem is exactly the same as the previous case where the materialized view log is not cleared. For details, refer to: materialized view log not cleared: http://yangtingkun.itpub.net/post/468/19939

However, the difference is that, in this example, the database chain is deleted only before the materialized view is deleted.

In the remote database, you can directly clear the message with the MVIEW_ID of 65:

SQL> EXEC DBMS_MVIEW.PURGE_MVIEW_FROM_LOG @ TEST2 (65)

The PL/SQL process is successfully completed.

SQL> SELECT COUNT (*) FROM MLOG $ _ T1 @ TEST2;

COUNT (*)
----------
0

This example only describes how to clear the most simple materialized view log content. For details about the materialized view log Content, refer:

Materialized view log structure: http://yangtingkun.itpub.net/post/468/20498

Oracle how to refresh Materialized View Based on materialized view log: http://yangtingkun.itpub.net/post/468/20584