How to unregister materialized view from source DB

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² seildi.

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 🙂

Posted in DBA
Leave a Reply

Your email address will not be published.