Re: Snapshot shows as INVALID in DBA_OBJECTs
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 [EMAIL PROTECTED] 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 [EMAIL PROTECTED] 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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
Re: Snapshot shows as INVALID in DBA_OBJECTs
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 [EMAIL PROTECTED] 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 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Dave Morgan INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
Re: Snapshot shows as INVALID in DBA_OBJECTs
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 [EMAIL PROTECTED] 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 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Dave Morgan INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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). __ 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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
RE: Snapshot shows as INVALID in DBA_OBJECTs
It's a bug, it is somewhere on metaqlink Walking on water and developing software from a specification are easy if both are frozen. Christopher R. Spence Oracle DBA Fuelspot -Original Message- Sent: Wednesday, July 18, 2001 1:16 PM To: Multiple recipients of list ORACLE-L 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 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Dave Morgan INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Christopher Spence INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
Snapshot shows as INVALID in DBA_OBJECTs
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 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Dave Morgan INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
