Re: difference between dba_snapshots and dba_mviews

From: Jared Still <jkstill_at_gmail.com>
Date: Fri, 24 Apr 2009 09:33:55 -0700
Message-ID: <bf46380904240933w720d988fm144164a919375a57_at_mail.gmail.com>

On Fri, Apr 24, 2009 at 7:14 AM, Dba DBA <oracledbaquestions_at_gmail.com>wrote:

> Oracle 10.1 on solaris
>
> First off, dba_snapshots appears to be obsolete because I don't see it
> listed as a data dictionary view in the 10g docs. I am trying to export a
> database and import it to a nother database. I have about 40 materialized
> views that have compile_state = 'ERROR' which are not being exported.
>

They may not be documented, but not necessarily obsolete.

There is still information in dba_snapshots that does not appear in dba_mviews.

A few years ago this was the case, as I wrote up some of the internals for mviews:

CURRENT_SNAPSHOTS and SNAPSHOT_ID columns. These two columns do not appear in the MVIEW_SNAPSHOT_LOGS view.

Looking at 11g that is still true.

The following may also still be true, though I don't have time to test it right now:

The DBA_REGISTERED_MVIEWS view may be used to show all snapshots created against
a master table, with one exception. If 2 or more MV's are created against a master table from databases with the same global name, only the most recently
created MV will appear in DBA_REGISTERED_MVIEWS. This view is based on the SYS.REG_SNAP$ table. This is documented behavior. What does that mean?

If for instance, you have are setting up a new production database that will replace
one currently in production, and it has the same global name as the production database,
creating MV's against the same master table will not cause another entry to appear in SYS.REG_SNAP$.

Rather, the existing entry in SYS.REG_SNAP$ that was created when the current
existing production database created its MV will be overwritten.

In this situation the only DD view that will show that will indicate the correct number of MV's registered against a master table is the DBA_SNAPSHOT_LOGS view.

Jared Still
Certifiable Oracle DBA and Part Time Perl Evangelist

--
http://www.freelists.org/webpage/oracle-l

Received on Fri Apr 24 2009 - 11:33:55 CDT