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/
> --
> 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).
>
>
>
>
__________________________________________________
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).
