Cleanup of materialized view registration information

Cleanup of materialized view registration information

The log cleanup of materialized views is performed automatically by Oracle. Oracle determines when the materialized view log is deleted based on the registration information on the Materialized View Graph table and the refresh of materialized views.
The cleanup of the registration information for the materialized view is done when the materialized view is deleted, but the removal of the materialized view registration information may be more complicated for the situation where the materialized view is built in the remote database.

If the materialized view is built locally, Oracle has all the data dictionary information, so all of the tests in this article are for the remote materialized view.
First, let's look at the example of clearing the materialized view log. This operation is performed entirely by Oracle, even if the user does not have the delete permission for the materialized view log.
First, log on to the remote database to establish the base table and materialized view logs for the test:
Sql> CONN [Email=test/test@test2]test/test@test2[/email] is connected.
Sql> CREATE TABLE T1 (ID number PRIMARY KEY);
Table has been created.
Sql> CREATE materialized VIEW LOG on T1;
The materialized view log has been created.
The following local database chains and materialized views are established:
Sql> CONN Usera/usera 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 [email=t1@test2]t1@test2[/email];
The materialized view was created.
Start the test below:
Sql> INSERT into [Email=t1@test2]t1@test2[/email] VALUES (1);
1 lines have been created.
Sql> COMMIT;
Submit completed.
Sql> SELECT COUNT (*) from [email=mlog$_t1@test2]mlog$_t1@test2[/email];
COUNT (*)
----------
1
Sql> EXEC Dbms_mview. REFRESH (' Mv_test ')
The PL/SQL process has completed successfully.
Sql> SELECT COUNT (*) from [email=mlog$_t1@test2]mlog$_t1@test2[/email];
COUNT (*)
----------
0
Visible, the materialized view log is automatically cleared after the materialized view is refreshed. However, because the connection user of the database chain is test has the delete permission to the MLOG$_T1. The following establishes a new user, giving only the MLOG$_T1 query permission:
Sql> CONN [Email=test/test@test2]test/test@test2[/email] is connected.
Sql> CREATE USER AAA identified by AAA;
User has created.
Sql> GRANT CONNECT, RESOURCE to AAA;
The authorization was successful.
Sql> GRANT SELECT on T1 to AAA;
The authorization was successful.
Sql> GRANT SELECT on mlog$_t1 to AAA;
The authorization was successful.
Because the global_names of my local database is set to False, a different user's database chain can be established to point to the TEST2 library.
Sql> CONN Usera/usera 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 [Email=test. T1@test2_new]test. T1@test2_new[/email];
COUNT (*)
----------
1
Sql> CREATE materialized VIEW mv_test2 REFRESH FAST as SELECT * from [Email=test. T1@test2_new]test. T1@test2_new[/email];
The materialized view was created.
Sql> INSERT into [Email=t1@test2]t1@test2[/email] VALUES (2);
1 lines have been created.
Sql> SELECT COUNT (*) from [email=mlog$_t1@test2]mlog$_t1@test2[/email];
COUNT (*)
----------
1
Sql> EXEC Dbms_mview. REFRESH (' Mv_test2 ')
The PL/SQL process has completed successfully.
Sql> SELECT COUNT (*) from [email=mlog$_t1@test2]mlog$_t1@test2[/email];
COUNT (*)
----------
0
Visible, the cleanup of the materialized view log is entirely Oracle's content operation, and has no relation to the user's permissions.
The purge of materialized view log depends on the registration information of materialized view, then the registration information of materialized view is cleared:
Sql> SELECT OWNER, NAME, Mview_site, mview_id
2 from [Email=user_registered_mviews@test2]user_registered_mviews@test2[/email];
OWNER NAME Mview_site mview_id
---------- -------------------- ------------------------------ ----------
UserA 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 [Email=user_registered_mviews@test2]user_registered_mviews@test2[/email];
No rows selected
Testing is not difficult to find when you delete a materialized view, Oracle automatically passes the deleted message to the primary site, and the primary site clears the registration information for the materialized view.
Is it that simple, yes. However, the prerequisite is that the database chain used to create materialized views exists and is available.
If you delete this database chain:
Sql> CREATE materialized VIEW mv_test2 REFRESH FAST as SELECT * from [Email=test. T1@test2_new]test. T1@test2_new[/email];
The materialized view was created.
Sql> SELECT OWNER, NAME, Mview_site, mview_id
2 from [Email=user_registered_mviews@test2]user_registered_mviews@test2[/email];
OWNER NAME Mview_site mview_id
---------- -------------------- ------------------------------ ----------
UserA 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 [Email=user_registered_mviews@test2]user_registered_mviews@test2[/email];
OWNER NAME Mview_site mview_id
---------- -------------------- ------------------------------ ----------
UserA Mv_test2 ytk. US. Oracle.com 65
Deleting the materialized view after deleting the database chain does not in itself cause an error, but the materialized view log for the primary site is not purged.
In addition, Oracle is not smart enough to take advantage of other equivalent database chains, it will only use the database chain specified in the materialized view definition.
Continue to observe the cleanup of the materialized view log below:
Sql> INSERT into [Email=t1@test2]t1@test2[/email] VALUES (3);
1 lines have been created.
Sql> COMMIT;
Submit completed.
Sql> SELECT COUNT (*) from [email=mlog$_t1@test2]mlog$_t1@test2[/email];
COUNT (*)
----------
1
Materialized view, because of the existence of the registration information of materialized views
Below, rebuild the database chain and the materialized view that you 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 [Email=test. T1@test2_new]test. T1@test2_new[/email];
The materialized view was created.
Sql> SELECT OWNER, NAME, Mview_site, mview_id
2 from [Email=user_registered_mviews@test2]user_registered_mviews@test2[/email];
OWNER NAME Mview_site mview_id
---------- -------------------- ------------------------------ ----------
UserA Mv_test2 ytk. US. Oracle.com 66
Sql> SELECT COUNT (*) from [email=mlog$_t1@test2]mlog$_t1@test2[/email];
COUNT (*)
----------
1
Because of the exact same information as owner, name, and Mview_site, Oracle did not create new registration information, but instead updated the registration information, the materialized view ID changed from 65 to 66.
But this time the materialized view creation did not clear the materialized view log.
and subsequent refresh operations cannot erase the materialized view log:
Sql> INSERT into [Email=t1@test2]t1@test2[/email] VALUES (4);
1 lines have been created.
Sql> COMMIT;
Submit completed.
Sql> SELECT COUNT (*) from [email=mlog$_t1@test2]mlog$_t1@test2[/email];
COUNT (*)
----------
2
Sql> EXEC Dbms_mview. REFRESH (' Mv_test2 ')
The PL/SQL process has completed successfully.
Sql> SELECT COUNT (*) from [email=mlog$_t1@test2]mlog$_t1@test2[/email];
COUNT (*)
----------
2
This time the materialized view log has not been cleared properly. Although registration information can be removed by removing the materialized view, it still does not solve the problem that materialized view logs cannot be purged.
Sql> DROP materialized VIEW mv_test2;
The materialized view has been deleted.
Sql> SELECT OWNER, NAME, Mview_site, mview_id
2 from [Email=user_registered_mviews@test2]user_registered_mviews@test2[/email];
No rows selected
Sql> CREATE materialized VIEW mv_test2 REFRESH FAST as SELECT * from [Email=test. T1@test2_new]test. T1@test2_new[/email];
The materialized view was created.
Sql> INSERT into [Email=t1@test2]t1@test2[/email] VALUES (5);
1 lines have been created.
Sql> COMMIT;
Submit completed.
Sql> SELECT COUNT (*) from [email=mlog$_t1@test2]mlog$_t1@test2[/email];
COUNT (*)
----------
3
Sql> EXEC Dbms_mview. REFRESH (' Mv_test2 ')
The PL/SQL process has completed successfully.
Sql> SELECT COUNT (*) from [email=mlog$_t1@test2]mlog$_t1@test2[/email];
COUNT (*)
----------
3
Sql> SELECT OWNER, NAME, Mview_site, mview_id
2 from [Email=user_registered_mviews@test2]user_registered_mviews@test2[/email];
OWNER NAME Mview_site mview_id
---------- -------------------- ------------------------------ ----------
UserA Mv_test2 ytk. US. Oracle.com 67
The query User_base_table_mviews view shows that the information mview_id 65 is not erased.
Sql> SELECT OWNER, MASTER, mview_id from [Email=user_base_table_mviews@test2]user_base_table_mviews@test2[/email] ;
OWNER MASTER mview_id
---------- ------------------------------ ----------
TEST T1 65
TEST T1 67
Now the problem is exactly the same as a materialized view log that was previously encountered.
The difference, however, is that one is caused by a database corruption rebuild, and in this case only because the database chain was deleted before the materialized view was deleted.
Below the remote database directly clear mview_id 65 information on it:
Sql> EXEC [Email=dbms_mview. Purge_mview_from_log@test2 (65]dbms_mview. Purge_mview_from_log@test2 (65[/email])
The PL/SQL process has completed successfully.
Sql> SELECT COUNT (*) from [email=mlog$_t1@test2]mlog$_t1@test2[/email];
COUNT (*)
----------
0

Reproduced from: http://itjaj.com/thread-2955-1-5.html