RE: Snapshot Logs Explanation Needed
Been a while since I worked with snapshots, but if I remember correctly, each record in a fast refresh snapshot keeps a mapping back to the rowid on the master. If you drop and recreate the master table, the mapping is hosed and updates/deletes do not propagate to the snapshot. Brian Norrell Manager, MPI Development QuadraMed 511 E John Carpenter Frwy, Su 500 Irving, TX 75062 (972) 831-6600 -Original Message- Sent: Wednesday, May 30, 2001 5:48 PM To: Multiple recipients of list ORACLE-L I would like to reorg many tables which have snapshots associated with them. I understand that if the master table is dropped the snapshot log is also dropped. When a log is dropped, oracle states that you need to do a complete refresh of the affected snapshot. My question is why? If you do not allow users to access the database with the master table, then you should not have any transactions that would be lost. Why can't you create a new log and continue to do a fast refresh? I am trying to avoid having to recreate all the snapshots due to the size and number that we have. Any thoughts? Thanks -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Tracy Rahmlow 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: Norrell, Brian 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 Logs Explanation Needed
Tracy, You may know that there were no modifications to the master table made from the time the snapshot log was dropped until it was recreated, but Oracle can't take that chance, otherwise data could get out of sync. That's why you have to either recreate your snapshot or do a complete refresh if the snapshot log is recreated. To avoid recreating the snapshots I would ask why you need to reorg your master tables (i.e. what do you hope to accomplish by this)? If you're doing it for defragmentation reasons, I suggest you check out the excellent white paper "How to stop defragmenting and start living..." to see if this is really necessary. http://www.vampired.net/articles/files/stopfrag.zip HTH, -- Anita --- Tracy Rahmlow <[EMAIL PROTECTED]> wrote: > I would like to reorg many tables which have > snapshots associated with them. I > understand that if the master table is dropped the > snapshot log is also > dropped. When a log is dropped, oracle states that > you need to do a complete > refresh of the affected snapshot. My question is > why? If you do not allow > users to access the database with the master table, > then you should not have > any transactions that would be lost. Why can't you > create a new log and > continue to do a fast refresh? I am trying to > avoid having to recreate all > the snapshots due to the size and number that we > have. Any thoughts? Thanks > > > -- > Please see the official ORACLE-L FAQ: > http://www.orafaq.com > -- > Author: Tracy Rahmlow > 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!? Get personalized email addresses from Yahoo! Mail - only $35 a year! http://personal.mail.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 Logs Explanation Needed
Correct. By the way: A good rule is to always keep the sequence "Table - Snapshot Log - Snapshot", ie. don't break the sequence, for instance by dropping and re-creating the snapshot log. If something happens to the snapshot log you should drop the snapshot, create the snapshot log, then re-create the snapshot. Mogens "Norrell, Brian" wrote: > Been a while since I worked with snapshots, but if I remember correctly, > each record in a fast refresh snapshot keeps a mapping back to the rowid on > the master. If you drop and recreate the master table, the mapping is hosed > and updates/deletes do not propagate to the snapshot. > > Brian Norrell > Manager, MPI Development > QuadraMed > 511 E John Carpenter Frwy, Su 500 > Irving, TX 75062 > (972) 831-6600 > > -Original Message- > Sent: Wednesday, May 30, 2001 5:48 PM > To: Multiple recipients of list ORACLE-L > > I would like to reorg many tables which have snapshots associated with them. > I > understand that if the master table is dropped the snapshot log is also > dropped. When a log is dropped, oracle states that you need to do a > complete > refresh of the affected snapshot. My question is why? If you do not allow > users to access the database with the master table, then you should not have > any transactions that would be lost. Why can't you create a new log and > continue to do a fast refresh? I am trying to avoid having to recreate all > the snapshots due to the size and number that we have. Any thoughts? > Thanks > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: Tracy Rahmlow > 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: Norrell, Brian > 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). -- Venlig hilsen Mogens Nørgaard Technical Director Miracle A/S, Denmark Web: http://MiracleAS.dk Mobile: +45 2527 7100 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mogens =?iso-8859-1?Q?N=F8rgaard?= 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 Logs Explanation Needed
Hi Anita, Ironically that is just what I am trying to do. (ie implement a storage plan which will address the issues discussed in the paper) To your other point, I understand oracle's position, but if I can prohibit changes to the master during the reorg/rebuild, is this still an issue? -- Forwarded by Tracy Rahmlow on 05/31/2001 12:37 PM --- Please respond to [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> cc: Tracy, You may know that there were no modifications to the master table made from the time the snapshot log was dropped until it was recreated, but Oracle can't take that chance, otherwise data could get out of sync. That's why you have to either recreate your snapshot or do a complete refresh if the snapshot log is recreated. To avoid recreating the snapshots I would ask why you need to reorg your master tables (i.e. what do you hope to accomplish by this)? If you're doing it for defragmentation reasons, I suggest you check out the excellent white paper "How to stop defragmenting and start living..." to see if this is really necessary. http://www.vampired.net/articles/files/stopfrag.zip HTH, -- Anita --- Tracy Rahmlow <[EMAIL PROTECTED]> wrote: > I would like to reorg many tables which have > snapshots associated with them. I > understand that if the master table is dropped the > snapshot log is also > dropped. When a log is dropped, oracle states that > you need to do a complete > refresh of the affected snapshot. My question is > why? If you do not allow > users to access the database with the master table, > then you should not have > any transactions that would be lost. Why can't you > create a new log and > continue to do a fast refresh? I am trying to > avoid having to recreate all > the snapshots due to the size and number that we > have. Any thoughts? Thanks > > > -- > Please see the official ORACLE-L FAQ: > http://www.orafaq.com > -- > Author: Tracy Rahmlow > 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!? Get personalized email addresses from Yahoo! Mail - only $35 a year! http://personal.mail.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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Tracy Rahmlow 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 Logs Explanation Needed
If you do a snapshot refresh then the snapshot log should be empty(correct?). Then I would think you could reorg the master table as long as you prohibit users from updating the master until the reorg and the recreating of the log is complete. Thus eliminating the need to do a complete refresh of the snapshot. -- Forwarded by Tracy Rahmlow on 05/31/2001 12:29 PM --- PM PST Please respond to [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> cc: Been a while since I worked with snapshots, but if I remember correctly, each record in a fast refresh snapshot keeps a mapping back to the rowid on the master. If you drop and recreate the master table, the mapping is hosed and updates/deletes do not propagate to the snapshot. Brian Norrell Manager, MPI Development QuadraMed 511 E John Carpenter Frwy, Su 500 Irving, TX 75062 (972) 831-6600 -Original Message- Sent: Wednesday, May 30, 2001 5:48 PM To: Multiple recipients of list ORACLE-L I would like to reorg many tables which have snapshots associated with them. I understand that if the master table is dropped the snapshot log is also dropped. When a log is dropped, oracle states that you need to do a complete refresh of the affected snapshot. My question is why? If you do not allow users to access the database with the master table, then you should not have any transactions that would be lost. Why can't you create a new log and continue to do a fast refresh? I am trying to avoid having to recreate all the snapshots due to the size and number that we have. Any thoughts? Thanks -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Tracy Rahmlow 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: Norrell, Brian 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: Tracy Rahmlow 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 Logs Explanation Needed
Hi Anita, Ironically that is just what I am trying to do. (ie implement a storage plan which will address the issues discussed in the paper) To your other point, I understand oracle's position, but if I can prohibit changes to the master during the reorg/rebuild, is this still an issue? -- Forwarded by Tracy Rahmlow on 05/31/2001 12:37 PM --- Please respond to [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> cc: Tracy, You may know that there were no modifications to the master table made from the time the snapshot log was dropped until it was recreated, but Oracle can't take that chance, otherwise data could get out of sync. That's why you have to either recreate your snapshot or do a complete refresh if the snapshot log is recreated. To avoid recreating the snapshots I would ask why you need to reorg your master tables (i.e. what do you hope to accomplish by this)? If you're doing it for defragmentation reasons, I suggest you check out the excellent white paper "How to stop defragmenting and start living..." to see if this is really necessary. http://www.vampired.net/articles/files/stopfrag.zip HTH, -- Anita --- Tracy Rahmlow <[EMAIL PROTECTED]> wrote: > I would like to reorg many tables which have > snapshots associated with them. I > understand that if the master table is dropped the > snapshot log is also > dropped. When a log is dropped, oracle states that > you need to do a complete > refresh of the affected snapshot. My question is > why? If you do not allow > users to access the database with the master table, > then you should not have > any transactions that would be lost. Why can't you > create a new log and > continue to do a fast refresh? I am trying to > avoid having to recreate all > the snapshots due to the size and number that we > have. Any thoughts? Thanks > > > -- > Please see the official ORACLE-L FAQ: > http://www.orafaq.com > -- > Author: Tracy Rahmlow > 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!? Get personalized email addresses from Yahoo! Mail - only $35 a year! http://personal.mail.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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Tracy Rahmlow 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 Logs Explanation Needed
If you do a snapshot refresh then the snapshot log should be empty(correct?). Then I would think you could reorg the master table as long as you prohibit users from updating the master until the reorg and the recreating of the log is complete. Thus eliminating the need to do a complete refresh of the snapshot. -- Forwarded by Tracy Rahmlow on 05/31/2001 12:29 PM --- PM PST Please respond to [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> cc: Been a while since I worked with snapshots, but if I remember correctly, each record in a fast refresh snapshot keeps a mapping back to the rowid on the master. If you drop and recreate the master table, the mapping is hosed and updates/deletes do not propagate to the snapshot. Brian Norrell Manager, MPI Development QuadraMed 511 E John Carpenter Frwy, Su 500 Irving, TX 75062 (972) 831-6600 -Original Message- Sent: Wednesday, May 30, 2001 5:48 PM To: Multiple recipients of list ORACLE-L I would like to reorg many tables which have snapshots associated with them. I understand that if the master table is dropped the snapshot log is also dropped. When a log is dropped, oracle states that you need to do a complete refresh of the affected snapshot. My question is why? If you do not allow users to access the database with the master table, then you should not have any transactions that would be lost. Why can't you create a new log and continue to do a fast refresh? I am trying to avoid having to recreate all the snapshots due to the size and number that we have. Any thoughts? Thanks -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Tracy Rahmlow 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: Norrell, Brian 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: Tracy Rahmlow 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 Logs Explanation Needed
Hi Tracy, Oracle still won't let you do a fast refresh if the creation time stamp on the snapshot log is more recent than the creation time stamp or last complete refresh of the snapshot. An attempt to do so will generate the dreaded ORA-12004: "REFRESH FAST cannot be used" (see note 1035524.6 "DIAGNOSING THE ORA-12004"). The bottom line is Oracle has to guarantee that snapshots will be in sync with their master tables and the only way it can do that is to prevent you from doing something that could potentially allow them to get out of sync. You don't mention what version you're on, but if you're on 8i and using PK-based snapshots, why not alter the master table storage conditions and use ALTER TABLE ... MOVE to move it into the new tablespace? I haven't personally tried this, but I don't see why it wouldn't work. I was also trying to think of a way the ON PREBUILT TABLE clause of the create snapshot/MV command in 8i could be used, but you'd still have to end up exporting/importing the table on the snapshot site or recreating it in some fashion (CTAS, sqlldr, etc...) as it will complain about the snapshot already existing and if you drop it you've lost the base table. HTH, -- Anita --- Tracy Rahmlow <[EMAIL PROTECTED]> wrote: > Hi Anita, > Ironically that is just what I am trying to do. (ie > implement a storage plan > which will address the issues discussed in the > paper) To your other point, I > understand oracle's position, but if I can prohibit > changes to the master > during the reorg/rebuild, is this still an issue? > -- Forwarded by Tracy Rahmlow on > 05/31/2001 12:37 PM > --- > > From: "A. Bardeen" <[EMAIL PROTECTED]>@fatcity.com > on 05/31/2001 04:51 AM PST > > Please respond to [EMAIL PROTECTED] > > Sent by: [EMAIL PROTECTED] > > > To: "Multiple recipients of list ORACLE-L" > <[EMAIL PROTECTED]> > cc: > Subject: Re: Snapshot Logs Explanation Needed > > > Tracy, > > You may know that there were no modifications to the > master table made from the time the snapshot log was > dropped until it was recreated, but Oracle can't > take > that chance, otherwise data could get out of sync. > That's why you have to either recreate your snapshot > or do a complete refresh if the snapshot log is > recreated. > > To avoid recreating the snapshots I would ask why > you > need to reorg your master tables (i.e. what do you > hope to accomplish by this)? If you're doing it for > defragmentation reasons, I suggest you check out the > excellent white paper "How to stop defragmenting and > start living..." to see if this is really necessary. > > http://www.vampired.net/articles/files/stopfrag.zip > > HTH, > > -- Anita > > --- Tracy Rahmlow <[EMAIL PROTECTED]> wrote: > > I would like to reorg many tables which have > > snapshots associated with them. I > > understand that if the master table is dropped the > > snapshot log is also > > dropped. When a log is dropped, oracle states > that > > you need to do a complete > > refresh of the affected snapshot. My question is > > why? If you do not allow > > users to access the database with the master > table, > > then you should not have > > any transactions that would be lost. Why can't > you > > create a new log and > > continue to do a fast refresh? I am trying to > > avoid having to recreate all > > the snapshots due to the size and number that we > > have. Any thoughts? Thanks > > > > > > -- > > Please see the official ORACLE-L FAQ: > > http://www.orafaq.com > > -- > > Author: Tracy Rahmlow > > 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!? > Get personalized email addresses from Yahoo! Mail - > only $35 > a year! http://personal.mail.yahoo.com/ > -- >
RE: Snapshot Logs Explanation Needed
OK, checked TFM and there are two type of fast refresh snapshots: primary key (new in 8) and rowid. For a rowid snapshot, when the table (EMP) is updated, a row is added to the snapshot log (MLOG$_EMP) containing mainly the type of action, timestamping info, and the rowid (MLOG$_EMP.M_ROW$$) of the affected record (rowid x deleted at 2pm). The snapshot object (type UNDEFINED in dba_objects) that lies underneath the table seen by the end users is akin to a table (it has an index) and has an extra column (also called M_ROW$$) that contains the rowid from the master table (rowid y is a copy of master rowid x). During a refresh, the changes from the snapshot log are applied to records in the shapshot with the appropriate *master* rowid (DELETE FROM snap.CAREGIVERS WHERE M_ROW$$ = x). So if you drop the table and recreate it without a full refresh, the M_ROW$$ values in the snapshot object are now invalid because they point to the emp.rowid before the reorg. For a primary key snapshot, there is no M_ROW$$ column. Instead, the primary key columns are placed in the snapshot log and used to identify the rows in the snapshot. Because rowids are not involved, a primary key snapshot CAN be fast refreshed (according to TFM) after a drop an recreate. However, there are some procedures that need to be called to let the system know you are doing a reorg so that it doesn't flip out when the table goes away. Clear as mud? HELP, AND YES I AM AN IDIOT. :) Brian Norrell Manager, MPI Development QuadraMed 511 E John Carpenter Frwy, Su 500 Irving, TX 75062 (972) 831-6600 -Original Message- Sent: Thursday, May 31, 2001 2:24 PM To: Multiple recipients of list ORACLE-L If you do a snapshot refresh then the snapshot log should be empty(correct?). Then I would think you could reorg the master table as long as you prohibit users from updating the master until the reorg and the recreating of the log is complete. Thus eliminating the need to do a complete refresh of the snapshot. -- Forwarded by Tracy Rahmlow on 05/31/2001 12:29 PM --- PM PST Please respond to [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> cc: Been a while since I worked with snapshots, but if I remember correctly, each record in a fast refresh snapshot keeps a mapping back to the rowid on the master. If you drop and recreate the master table, the mapping is hosed and updates/deletes do not propagate to the snapshot. Brian Norrell Manager, MPI Development QuadraMed 511 E John Carpenter Frwy, Su 500 Irving, TX 75062 (972) 831-6600 -Original Message- Sent: Wednesday, May 30, 2001 5:48 PM To: Multiple recipients of list ORACLE-L I would like to reorg many tables which have snapshots associated with them. I understand that if the master table is dropped the snapshot log is also dropped. When a log is dropped, oracle states that you need to do a complete refresh of the affected snapshot. My question is why? If you do not allow users to access the database with the master table, then you should not have any transactions that would be lost. Why can't you create a new log and continue to do a fast refresh? I am trying to avoid having to recreate all the snapshots due to the size and number that we have. Any thoughts? Thanks -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Tracy Rahmlow 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: Norrell, Brian 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: Tracy Rahmlow INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists
