meaning of "Used By Materialized View" tab on the materialized view logs data grid. Why does it show apparently extra mview definitions?

Toad for Oracle
1

after navigating to mview logs under a specific schema, and picking a specific mview log to review, there is a tab available called “Used By Materialized View” which I presume shows the mviews that use this mview log. Below is a sample of the data from one such tab.

**NOTICE, there appear to be TWO mviews that use the mview log I am interested in. ** To my knowledge however, the second mview (MV_BUS_CLSS_AGNCY_INDU) does NOT in fact reference the mview log in question. Is there a known bug with this tab? Every mview log I review appears to always show one extra mview that is NOT related.

Can someone confirm this?

Is it possible to see the underlying query from toad that retrieved this info?

“Parameter”,“Value”

“Owner”,“CDRMV”

“Name”,“MV_UW_STATE_FORM_SUPP_INFO”

“Snapshot Site”,“CDR3D.THEHARTFORD.COM”

“Can Use Log”,“YES”

“Updatable”,“NO”

“Refresh Method”,“PRIMARY KEY”

“Version”,“ORACLE 8 SNAPSHOT”

“Query Text”,“SELECT ““UW_STATE_FORM_SUPP_INFO””.”“UW_ST_FORM_SUPP_INFO_ID”"

““UW_STATE_FORM_SUPP_INFO_ID””,

““UW_STATE_FORM_SUPP_INFO””."“INDUST_SBGRP_ID”" ““INDUST_SBGRP_ID””,

““UW_STATE_FORM_SUPP_INFO””."“STT_ID”" ““STT_ID””,

““UW_STATE_FORM_SUPP_INFO””."“SUPP_INFO_PRIRTY_NUM”"

““SUPP_INFO_PRIRTY_NUM””,

““UW_STATE_FORM_SUPP_INFO””."“DLT_FLAG”" ““DLT_FLAG””,

““UW_STATE_FORM_SUPP_INFO””."“SUPP_INFO_LINK_ADDR”" ““SUPP_INFO_LINK_ADDR””,

““UW_STATE_FORM_SUPP_INFO””."“SUPP_INFO_LINK_TXT”" ““SUPP_INFO_LINK_TXT””

FROM ““UW_STATE_FORM_SUPP_INFO””@cdr ““UW_STATE_FORM_SUPP_INFO””

WHERE NVL ("“UW_STATE_FORM_SUPP_INFO”"."“DLT_FLAG”", ‘N’) = ‘N’"

“--------------------------”,"--------------------------"

“Owner”,“CDRMV”

“Name”,“MV_BUS_CLSS_AGNCY_INDU”

“Snapshot Site”,“CDR3D.THEHARTFORD.COM”

“Can Use Log”,“YES”

“Updatable”,“NO”

“Refresh Method”,“PRIMARY KEY”

“Version”,“ORACLE 8 SNAPSHOT”

“Query Text”,"SELECT BUS_CLSS_AGNCY_INDU_ID,

INDSTRY_GRP_ID ,

STT_ID,

BSNSS_CLSS_CASE_ID ,

AGNCY_CD,

DSCRPTR_CD,

BSNSS_CLSS_CD_TYPE_NM

FROM BUSINESS_CLASS_AGENCY_INDUSTRY@cdr BUSINESS_CLASS_AGENCY_INDUSTRY"

2

Hi Kevin,

No, There is no bug there that I know of.

If you go to Main Menu -> Database -> Spool to Screen, you can see all of the queries that Toad runs in the background.

So., when I turn that on and go to “Used By MView”, I see this query spooled:

SELECT rs.owner, rs.name, rs.snapshot_site, rs.can_use_log, rs.updatable,
rs.refresh_method, rs.version, rs.query_txt
FROM sys.DBA_REGISTERED_SNAPSHOTS rs,
sys.DBA_SNAPSHOT_LOGS sl
WHERE sl.SNAPSHOT_ID = rs.SNAPSHOT_ID
AND sl.LOG_OWNER = :lo
AND sl.MASTER = :m;

This is what we use to populate that grid.

-John

1 Like
3

Thanks John, you are great. I was not aware of the spool/screen feature for following SQL being executed by Toad. That tip is maybe the best thing I learned in the last six months. I am going to look into that in depth.

As to the MVIEW issue; it appears to be an Oracle data corruption issue. Indeed the query returns what the metadata says. But the metadata is wrong, or there is something much deeper about how Oracle relates things than I understand. I know my systems very well and the reported list of mviews by the query is incorrect. I will do more research and if I find something useful will add it to this discussion. I was seeing the actual SQL Toad was using that gave me the final confirmation.

Thanks again. Kevin Meade.

4

You’re welcome.

If you discover there is some Oracle bug and I can work around it by changing my query (or if the query is just wrong), please let me know.

5

Just gonna take the opportunity here to second the John is great! And how informative the spool SQL is, too!

6

aww, thanks y’all.

7

After some research, there appears to clearly be a bug in the Oracle metadata. There is some issue with REMOTE MVIEWS and the use of the SNAPID from the remote database. Oracle is incorrectly associating different MVIEWS to the wrong master tables for what appears to be a historical mixup. This does not appear to affect functionality. Using my knowledge of my database, I knew which items were correct and which were not, and could proceed past my issue by doing the proper steps. The incorrect references did not stop me which means at least for my case, they are not being referenced. Again it does not appear to be a Toad issue, but an Oracle issue.

Summary of my issue:

  1. a model change required a change to a table.

  2. this table has a mview log on it.

  3. this table is used by a remote mview (mview on another database).

  4. attempted to rename the table to save it for easy recovery in case I messed up bigtime, but rename failed (because of the remote mview dependence on the local mview log).

  5. looked into mview metadata to determine which mview was responsible, and saw two. I knew one was wrong, and this sparked the discussion.

  6. getting the toad query confirmed what I thought, that is was likely an Oracle bug.

  7. proceeded with the proper workflow anyway, ignoring the incorrect metadata entries, and it worked fine.

a. refresh remote mview (makes sure all in in synch)

b. drop local mview log

c. rename local table

d. recreate mview log (not really needed)

f. created new version of table

g. remapped data from old table to new

h. created new mview log

i. drop/recreate remote mview with appropriate newquery (could have just dropped it in the beginning)

8

Thanks for the follow up.