Re: Snapshot shows as INVALID in DBA

From: A. Bardeen <abardeen1_at_yahoo.com>
Date: Tue, 24 Jul 2001 08:15:19 -0700
Message-ID: <F001.003535B6.20010724081044@fatcity.com>

Dave,

There are several issues with MV's/snapshots not displaying info correctly in various DBA/ALL/USER views. So far all that I've found are annoying, but don't interfere with refreshes or any other operations, and are fixed in 9.0.1.

This is probably due to the fact that they really are two different beasts behind the scenes although they're used synonymously and share much of the same code base. I have a tendency to think of snapshots for distributed operations (the queries involve a dblink) and MV's for data warehousing operations (everything goes against local tables).

When a snapshot is being refreshed, as part of the set-up phase the refresh date is set to 1/1/1950 (in a sense, the "beginning of time"). This way if the refresh is unsuccessful, Oracle knows that a complete refresh needs to be done the next time. Since 8.x, the date shown under LAST_REFRESH in DBA_SNAPSHOTS has incorrectly shown 1/1/1950 (bug 557451, fixed in 8.1.7.2, 9i; note 1036859.6). Instead query DBA_SNAPSHOT_REFRESH_TIMES. HTH,

Anita
- Dave Morgan <dave.morgan_at_cybersurf.net> wrote:
  > Thanks Anita,
  > I filed a TAR, altered my catalog.sql,
  > recompiled 360 invalid objects, and now:
  >
  > It shows up in DBA_OBJECTS as a MATERIALIZED VIEW,
  > but the status is still INVALID, and the last
  > refresh data is shown as 1950!!!!
  >
  > I don't think this morning is 1950 but I've been
  > wrong before :)
  >
  > Sigh ......
  >
  > And metaStink is basically down this morning so
  > .....
  >
  > I'm still confused :)
  > Dave
  >
  > "A. Bardeen" wrote:
  > >
  > > Dave,
  > >
  > > As someone else pointed out, this is a known bug
  > > (1188948).
  > >
  > > The issue is not with the dd, but that the view
  > for
  > > DBA_OBJECTS did not get updated to handle the new
  > > object type value for materialized views. If you
  > > check the view definition (should be in
  > catalog.sql)
  > > you'll see a decode statement that determines the
  > > object type. It doesn't have an entry for type
  > 42,
  > > the new type for materialized views/snapshots, so
  > > that's why they're listed as "undefined."
  > >
  > > You can manually change the view definition, just
  > keep
  > > in mind that you'll need to change it again after
  > > applying a patchset if the patchset replaces
  > > catalog.sql.
  > >
  > > It doesn't interfere with the way the snapshot/MV
  > > works, so it's considered annoying, but harmless.
  > The
  > > script is fixed in 9i.
  > >
  > > HTH,
  > >
  > > -- Anita
  > >
  > > --- Dave Morgan <dave.morgan_at_cybersurf.net> wrote:
  > > > Hi All,
  > > > The subject says it all, 8.1.7 on Solaris
  > 2.8
  > > >
  > > > The snapshot is accessable and correct.
  > > >
  > > > Anyone know a (supported) way to clean up
  > the data
  > > > dictionary?
  > > >
  > > > TIA
  > > > Dave
  > > >
  >
  > --
  > Dave Morgan
  > DBA, Cybersurf
  > Office: 403 777 2000 ext 284

Do You Yahoo!?
Make international calls for as low as $.04/minute with Yahoo! Messenger http://phonecard.yahoo.com/

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: A. Bardeen
  INET: abardeen1_at_yahoo.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Received on Tue Jul 24 2001 - 10:15:19 CDT

Re: Snapshot shows as INVALID in DBA_OBJECTs