Evidently my original message was bounced back, so I'm
resending.
Date: Fri, 1 Jun 2001 11:25:20 -0700 (PDT)
To: ORACLE-L_at_fatcity.com, BNorrell_at_QuadraMed.com,
Tracy.Rahmlow_at_aexp.com
In-Reply-To:
<F001.0031737E.20010531162735_at_fatcity.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Brian,
You are absolutely correct and I was wrong. I went
back and RTFM and for PK-based snapshots
DBMS_SNAPSHOT.BEGIN_TABLE_REORGANIZATION and
END_TABLE_REORGANIZATION exist for just this purpose.
Thanks Brian for setting me straight.
Before I checked the manual, however, I tried some
tests myself (8.1.6 on Win2K) and I may have uncovered
a new bug. I was unable to find an existing bug on
metalink, but I want to do some more tests before I
report it.
If you drop and recreate the snapshot log without
calling the procedure above, the next fast refresh
will not report an error, but it removes the rows from
the snapshot log and they are NOT refreshed to the
snapshot which means the snapshot will be out of sync!
Bad news. Subsequent fast refreshes work properly.
I'm just listing an excerpt from the spool file,
SVRMGR> select * from test;
COL1 COL2 COL3
----- ----- -----
1 2 3
2 4 6
3 6 9
3 rows selected.
SVRMGR> create snapshot snaptest refresh fast as
select * from test;
Statement processed.
SVRMGR> select * from snaptest;
COL1 COL2 COL3
----- ----- -----
1 2 3
2 4 6
3 6 9
3 rows selected.
SVRMGR> drop snapshot log on test;
Statement processed.
SVRMGR> create snapshot log on test;
Statement processed.
SVRMGR> -- the primary key option is not needed since
that is the default
SVRMGR> insert into test values (seq1.nextval,
seq1.nextval*2, seq1.nextval*3);
1 row processed.
SVRMGR> commit;
Statement processed.
SVRMGR> select count(*) from test;
COUNT
4
1 row selected.
SVRMGR> select count(*) from mlog$_test;
COUNT
1
1 row selected.
SVRMGR> select count(*) from snaptest;
COUNT
3
1 row selected.
SVRMGR> execute dbms_snapshot.refresh('SNAPTEST','F');
Statement processed.
SVRMGR> select count(*) from mlog$_test;
COUNT
0
1 row selected.
SVRMGR> select count(*) from snaptest;
COUNT
3
1 row selected.
SVRMGR> execute dbms_snapshot.refresh('SNAPTEST','F');
Statement processed.
SVRMGR> select count(*) from test;
COUNT
4
1 row selected.
SVRMGR> select count(*) from mlog$_test;
COUNT
0
1 row selected.
SVRMGR> select count(*) from snaptest;
COUNT
3
1 row selected.
SVRMGR> -- snapshot is now out of sync!
SVRMGR> insert into test values (seq1.nextval,
seq1.nextval*2, seq1.nextval*3);
1 row processed.
SVRMGR> commit;
Statement processed.
SVRMGR> select count(*) from test;
COUNT
5
1 row selected.
SVRMGR> select count(*) from mlog$_test;
COUNT
1
1 row selected.
SVRMGR> select count(*) from snaptest;
COUNT
3
1 row selected.
SVRMGR> execute dbms_snapshot.refresh('SNAPTEST','F');
Statement processed.
SVRMGR> select count(*) from test;
COUNT
4
1 row selected.
SVRMGR> select count(*) from mlog$_test;
COUNT
0
1 row selected.
SVRMGR> select count(*) from snaptest;
COUNT
4
1 row selected.
SVRMGR> select * from snaptest;
COL1 COL2 COL3
----- ----- -----
1 2 3
2 4 6
3 6 9
5 10 15
4 rows selected.
SVRMGR> -- Notice the gap, row w/values 4,8,12 has
been lost
I'll let y'all know what I find out on the bug.
- Anita
- "Norrell, Brian" <BNorrell_at_QuadraMed.com> wrote:
> 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 ORACLE-L_at_fatcity.com
>
> Sent by: root_at_fatcity.com
>
>
> To: "Multiple recipients of list ORACLE-L"
> <ORACLE-L_at_fatcity.com>
> 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
>
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: 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 Sat Jun 02 2001 - 08:35:49 CDT
