Oracle

Which DB my mview resides???

Posted Anand

Today one of my friend called me and asked –

Can we determine the database and the mview, from the database where mview log resides?

The mview log was occupying lot of space which he wanted to drop and recreate, which was followed by complete refresh of mview (which used that mview log).He had no idea which database and which mviews were using that mview log.
So, here is how we can determine the mview and database name.
Session1
==========
Created mview log on scott.emp table in MATRIX database

23:34:55 [email protected]> SELECT master, log_table, rowids, primary_key FROM user_snapshot_logs;
no rows selected
Elapsed: 00:00:00.01
23:35:01 [email protected]> create materialized view log on emp;
Materialized view log created.
Elapsed: 00:00:00.06
23:35:04 [email protected]> SELECT master, log_table, rowids, primary_key FROM user_snapshot_logs;
MASTER                         LOG_TABLE                      ROW PRI
------------------------------ ------------------------------ --- ---
EMP                            MLOG$_EMP                      NO  YES
Elapsed: 00:00:00.01

Session 2
===========
Logged in to another database ORCL, create a public database link to [email protected]

23:36:20 [email protected]> create public database link mv_dblink connect to scott identified by tiger using 'MATRIX';
Database link created.
Elapsed: 00:00:00.01
23:36:31 [email protected]>
23:36:31 [email protected]>
23:36:31 [email protected]> select * from [email protected]_dblink;
D
-
X
Elapsed: 00:00:00.12

Session 2
============
Created fast refresh mview (emp_mv) in ANAND schema – ORCL db

23:36:39 [email protected]> conn anand/anand123
Connected.
23:36:49 [email protected]>
23:36:50 [email protected]>
23:36:50 [email protected]>
23:36:50 [email protected]>
23:36:50 [email protected]>
23:36:50 [email protected]> create materialized view emp_mv
23:37:14   2  build immediate
23:37:21   3  refresh fast
23:37:27   4  as
23:37:31   5  select * from [email protected]_dblink;
Materialized view created.
Elapsed: 00:00:01.25
23:37:53 [email protected]>
23:37:53 [email protected]>
23:37:54 [email protected]>
23:37:54 [email protected]> select * from emp_mv;
     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17-DEC-80        800                    20
      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30
      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30
      7566 JONES      MANAGER         7839 02-APR-81       2975                    20
      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30
      7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30
      7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10
      7788 SCOTT      ANALYST         7566 19-APR-87       3000                    20
      7839 KING       PRESIDENT            17-NOV-81       5000                    10
      7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0         30
      7876 ADAMS      CLERK           7788 23-MAY-87       1100                    20
      7900 JAMES      CLERK           7698 03-DEC-81        950                    30
      7902 FORD       ANALYST         7566 03-DEC-81       3000                    20
      7934 MILLER     CLERK           7782 23-JAN-82       1300                    10
14 rows selected.
Elapsed: 00:00:00.09

Now suppose you are not aware of the mview and you are asked to determine the mview and the database it resides in, from the database where mview log exists, how will you do it??
Session 1
==========

23:44:47 [email protected]> SELECT r.NAME mview_name, snapid, NVL(r.mview_site, 'not registered') mview_site, snaptime FROM   sys.slog$ s, dba_registered_mviews r WHERE  s.snapid=r.mview_id(+) AND mowner LIKE UPPER('&owner') AND MASTER LIKE UPPER('&table_name');
Enter value for owner: scott
Enter value for table_name: emp
old   1: SELECT r.NAME mview_name, snapid, NVL(r.mview_site, 'not registered') mview_site, snaptime FROM   sys.slog$ s, dba_registered_mviews r WHERE  s.snapid=r.mview_id(+) AND mowner LIKE UPPER('&owner') AND MASTER LIKE UPPER('&table_name')
new   1: SELECT r.NAME mview_name, snapid, NVL(r.mview_site, 'not registered') mview_site, snaptime FROM   sys.slog$ s, dba_registered_mviews r WHERE  s.snapid=r.mview_id(+) AND mowner LIKE UPPER('scott') AND MASTER LIKE UPPER('emp')
MVIEW_NAME                         SNAPID MVIEW_SITE           SNAPTIME
------------------------------ ---------- -------------------- ---------
EMP_MV                                 42 ORCL                 01-FEB-11
Elapsed: 00:00:00.01
23:44:50 [email protected]>

In the above query, you need to provide the schema name and table name for which mview log is created as input.The output is the mview name and the database name where it exists.
Hope this helps 🙂

Leave a Reply