If a remote MV is no longer available or if you move to a new server because of data migration, version upgrade etc. and somehow don’t need the old server anymore. However, if unused remote MV is still registered with any of the tables in your DB. You can unregistered it from your main db. In addition, you can delete unused records from MV log table.
Let’s show an example regarding this topic.
--MAIN DB
CREATE TABLE T_OBJECTS_DB1
AS
SELECT * FROM DBA_OBJECTS WHERE OBJECT_ID IS NOT NULL;
ALTER TABLE T_OBJECTS_DB1 MODIFY OBJECT_ID PRIMARY KEY;
--Let's create materialized view log in the main db.
CREATE MATERIALIZED VIEW LOG ON T_OBJECTS_DB1
NOCACHE
NOLOGGING
WITH PRIMARY KEY;
SELECT LOG_TABLE FROM DBA_MVIEW_LOGS WHERE MASTER = 'T_OBJECTS_DB1';
LOG_TABLE
------------------------------
MLOG$_T_OBJECTS_DB1
So, we can create materialized view in remote database. The necessary privileges are assumed to be granted.
--REMOTE DB
CREATE MATERIALIZED VIEW MV_OBJECTS_DB1
NOCACHE
NOLOGGING
NOCOMPRESS
BUILD IMMEDIATE
REFRESH FAST ON DEMAND
WITH PRIMARY KEY
AS
SELECT * FROM MAIN_USER.T_OBJECTS_DB1@MAINDB;
SELECT COUNT(*) FROM MV_OBJECTS_DB1;
COUNT(*)
----------
88557
--MAIN DB
SQL> INSERT INTO T_OBJECTS_DB1
2 SELECT
3 OWNER, OBJECT_NAME, SUBOBJECT_NAME,
4 ((SELECT MAX(OBJECT_ID) FROM T_OBJECTS_DB1) + ROWNUM),
5 DATA_OBJECT_ID, OBJECT_TYPE, CREATED, LAST_DDL_TIME,
6 TIMESTAMP, STATUS, TEMPORARY, GENERATED, SECONDARY,
7 NAMESPACE, EDITION_NAME
8 FROM DBA_OBJECTS WHERE OBJECT_ID IS NOT NULL AND ROWNUM <= 10;
10 sat²rlar² yarat²ld².
SQL> COMMIT;
Kaydetme tamamland².
SQL> SET LINESIZE 100
SQL> COLUMN OBJECT_ID FORMAT A10
SQL> COLUMN SNAPTIME$$ FORMAT A20
SQL> COLUMN DMLTYPE$$ FORMAT A5
SQL> COLUMN OLD_NEW$$ FORMAT A5
SQL> COLUMN CHANGE_VECTOR$$ FORMAT A5
SQL> COLUMN XID$$ FORMAT A30
SQL> COLUMN OBJECT_ID FORMAT 999999999999
SQL> COLUMN XID$$ FORMAT 9999999999999999999
SQL>
SQL> SELECT * FROM MLOG$_T_OBJECTS_DB1;
OBJECT_ID SNAPTIME$$ DMLTY OLD_N CHANG XID$$
------------- -------------------- ----- ----- ----- --------------------
634076 01/01/4000 I N FEFF 283726810884542535
634077 01/01/4000 I N FEFF 283726810884542535
634078 01/01/4000 I N FEFF 283726810884542535
634079 01/01/4000 I N FEFF 283726810884542535
634080 01/01/4000 I N FEFF 283726810884542535
634081 01/01/4000 I N FEFF 283726810884542535
634082 01/01/4000 I N FEFF 283726810884542535
634083 01/01/4000 I N FEFF 283726810884542535
634084 01/01/4000 I N FEFF 283726810884542535
634085 01/01/4000 I N FEFF 283726810884542535
10 sat²rlar² seildi.
SQL> COLUMN OWNER FORMAT A20
SQL> COLUMN NAME FORMAT A20
SQL> COLUMN MVIEW_SITE FORMAT A20
SQL> COLUMN MVIEW_ID FORMAT A20
SQL> COLUMN MVIEW_ID FORMAT 999999999999
SQL>
SQL> SELECT OWNER, NAME, MVIEW_SITE, MVIEW_ID FROM DBA_REGISTERED_MVIEWS WHERE NAME = 'MV_OBJECTS_DB1';
OWNER NAME MVIEW_SITE MVIEW_ID
-------------------- -------------------- -------------------- -------------
HR MV_OBJECTS_DB1 DB1 1794
So, as you can see from the above code. You can select DBA_REGISTERED_MVIEWS view to get all registered MVs belong to your local tables. You can delete all log records and also unregister the remote MV at master site.
SQL> BEGIN
2 DBMS_MVIEW.PURGE_MVIEW_FROM_LOG(MVIEW_ID => 1794);
3 END;
4 /
PL/SQL yordam² ba■ar²yla tamamland².
SQL>
SQL> SELECT COUNT(*) FROM MLOG$_T_OBJECTS_DB1;
COUNT(*)
----------
0
SQL> BEGIN
2 DBMS_MVIEW.UNREGISTER_MVIEW(
3 MVIEWOWNER => 'HR',
4 MVIEWNAME => 'MV_OBJECTS_DB1',
5 MVIEWSITE => 'DB1'
6 );
7 END;
8 /
PL/SQL yordam² ba■ar²yla tamamland².
SQL>
SQL> SELECT COUNT(*) FROM DBA_REGISTERED_MVIEWS WHERE NAME = 'MV_OBJECTS_DB1';
COUNT(*)
----------
0
I hope this helps you 🙂