Re: Snapshot problem on Oracle 8.0.5.1 (urgent)

What you may be hitting is the default date that you don't need to worry about (see below).

HTH. Pete

Article-ID:         <PrSol:2113934.6>
For-Problem:        <Pr:1068195.6>
Circulation:        PUBLISHED (EXTERNAL)
Platform:           GENERIC
Folder:             server.Rdbms.Distributed.Snapshots
Subject:            DO NOTHING, SNAPTIME$$ WILL BE UPDATED CORRECTLY ON
REFRESH
Author:             SSWHITE.US
Last-Updated:       27-JUL-99
Component:          RDBMS_V08.XX

Solution Description:

When DML is performed on a MASTER table of a snapshot, an entry is created in mlog$ for each row affected.

If there is only ONE snapshot refreshing that master table, the rows will be purged once that snapshot refreshes.

If there is more than one snapshot refreshing that master table, then SNAPTIME$$ is used to keep track of the time the FIRST snapshot refreshed
that row.

    Example:

    Lets say we have snapshots S_TEST and S_TEST2 and master table TEST.

    SQL> insert into test values (4);

    1 row created.

    SQL> commit;

    Commit complete.

    SQL> select m_row$$, to_char(snaptime$$,'mm-dd-yyyy hh:mi:ss'), dmltype$$

      2 from mlog$_test;

    M_ROW$$                TO_CHAR(SNAPTIME$$,  D
    ---------------------  -------------------  -
    AAAAjfAACAAAAIaAAD     01-01-4000 12:00:00  I

    Let's refresh only ONE snapshot: s_test ............

    SQL> exec dbms_snapshot.refresh('s_test','f');

    PL/SQL procedure successfully completed.

    Once we refresh one snapshot, the SNAPTIME$$ column will be updated     with the time of the refresh.

    SQL> select m_row$$, to_char(snaptime$$,'mm-dd-yyyy hh:mi:ss'), dmltype$$

      2 from mlog$_test;

    M_ROW$$ TO_CHAR(SNAPTIME$$, D

• ------------------- - AAAAjfAACAAAAIaAAD 07-19-1999 03:56:39 I

    Once all snapshots have refreshed all records in the snapshot log,     those records will be purged.

Solution Explanation:

There is nothing you need to do when you see SNAPTIME$$ of 01-JAN-4000 because this is a default date.

Ho Yat-Fan wrote:

> Hello all,
>
> I created a snapshot using a database link from Oracle 8.0.5.1
> (on WinNT) to Oracle 7.3.4 using the following statement:
>
> CREATE SNAPSHOT BUS_DEVICE_HISTORY
> TABLESPACE STAB_TS
> REFRESH FORCE
> START WITH SYSDATE
> NEXT SYSDATE+1
> WITH ROWID
> AS SELECT * FROM BUS_DEVICE_HISTORY_at_kcerop_link.WORLD;
>
> It's successful and there is also no errors when
> I issue (using SYS) "exec dbms_snapshot.refresh_all;"
>
> However, if I use "select to_char(start_with,'YYYYMMDD')
> from dba_snapshots". I observer the strange thing that
> the date appears to be 00000000. I tried methods like
> altering the snapshot with "start_with" being '17-AUG-1999'
> but something strange such as 19627968 (something like
> this) appears and also that the dbms_snapshot.refresh_all
> did not really successfully get the remote data.
>
> Any advice??
>
> Many thanks and if possible, please cc: a copy of your
> reply to my another account fan.ho_at_alumni.ust.hk
>
> --
> Yat-Fan Ho
> Software Support Analyst
> KCRC, Hong Kong
>
> Sent via Deja.com http://www.deja.com/
> Share what you know. Learn what you don't.

--
Regards

Pete

Received on Mon Aug 16 1999 - 12:57:12 CDT