Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> MEA CULPA: Snapshot Logs Explanation Needed

MEA CULPA: Snapshot Logs Explanation Needed

From: A. Bardeen <abardeen1_at_yahoo.com>
Date: Sat, 02 Jun 2001 06:35:49 -0700
Message-ID: <F001.003192FF.20010602062521@fatcity.com>

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.


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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US