FAQ
Hello Lists,

I have a set of snapshots refreshing in FAST mode
using snapshot logs. The snapshot log is keep-on
growing as it should and after certain amount of time,
I had to expand the tablespace space under which the
snapshot log is located.

My question is, Is there a way to periodically
truncate or remove records from snapshot logs? Of
course we can make something on our own by scheduling
some delete jobs, but, Is there any default method
available in Oracle?

Any input would be highly appreciated.

Thanks in advance,
Muru

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: Bala Muru
INET: muru_oracle_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).

Search Discussions

  • Fowler, Kenneth R at Feb 14, 2001 at 6:03 pm
    I have had the same problem with Oracle 8.1.6 on Solaris 2.6. What version
    of Oracle are you using??

    -----Original Message-----
    Sent: Wednesday, February 14, 2001 12:16 PM
    To: Multiple recipients of list ORACLE-L

    Hello Lists,

    I have a set of snapshots refreshing in FAST mode
    using snapshot logs. The snapshot log is keep-on
    growing as it should and after certain amount of time,
    I had to expand the tablespace space under which the
    snapshot log is located.

    My question is, Is there a way to periodically
    truncate or remove records from snapshot logs? Of
    course we can make something on our own by scheduling
    some delete jobs, but, Is there any default method
    available in Oracle?

    Any input would be highly appreciated.

    Thanks in advance,
    Muru

    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: Bala Muru
    INET: muru_oracle_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).
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Fowler, Kenneth R
    INET: kenneth_r_fowler_at_groton.pfizer.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).
  • Scott.Shafer_at_dcpds.cpms.osd.mil at Feb 14, 2001 at 7:52 pm
    See dbms_snapshot.purge_log() in the PL/SQL Supplied Packages Reference.

    PURGE_LOG procedure

    This procedure purges rows from the snapshot log.

    Syntax

    DBMS_SNAPSHOT.PURGE_LOG (

    master IN VARCHAR2,
    num IN BINARY_INTEGER := 1,
    flag IN VARCHAR2 := 'NOP');
    ----------------------------------------------------------------------------

    --Scott Shafer
    San Antonio, TX
    -----Original Message-----
    From: Bala Muru [SMTP:muru_oracle_at_yahoo.com]
    Sent: Wednesday, February 14, 2001 11:16 AM
    To: Multiple recipients of list ORACLE-L
    Subject: Are snapshot logs purged?

    Hello Lists,

    I have a set of snapshots refreshing in FAST mode
    using snapshot logs. The snapshot log is keep-on
    growing as it should and after certain amount of time,
    I had to expand the tablespace space under which the
    snapshot log is located.

    My question is, Is there a way to periodically
    truncate or remove records from snapshot logs? Of
    course we can make something on our own by scheduling
    some delete jobs, but, Is there any default method
    available in Oracle?

    Any input would be highly appreciated.

    Thanks in advance,
    Muru
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author:
    INET: Scott.Shafer_at_dcpds.cpms.osd.mil

    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).
  • Paul Baumgartel at Feb 14, 2001 at 8:10 pm
    The more useful course of action, though, would be to find out why the
    snapshot logs keep growing. Such a situation usually means that the master
    site thinks other snapshots exist against the log table, and since they're
    never refreshed, the log entries are never purged.

    You might want to consider (in this order) dropping the snapshots, dropping
    the logs, re-creating the logs, and re-creating the snapshots. Perform some
    DML on the master tables, then do fast refreshes and ensure that the logs
    are empty.

    Paul Baumgartel
    InstiPro, Inc.
    paul.baumgartel_at_instipro.com
    212 813-0829 x103 (office)
    917 549-4717 (mobile)

    -----Original Message-----

    Sent: Wednesday, February 14, 2001 2:31 PM
    To: Multiple recipients of list ORACLE-L

    See dbms_snapshot.purge_log() in the PL/SQL Supplied Packages Reference.

    PURGE_LOG procedure

    This procedure purges rows from the snapshot log.

    Syntax

    DBMS_SNAPSHOT.PURGE_LOG (

    master IN VARCHAR2,
    num IN BINARY_INTEGER := 1,
    flag IN VARCHAR2 := 'NOP');
    ----------------------------------------------------------------------------

    --Scott Shafer
    San Antonio, TX
    -----Original Message-----
    From: Bala Muru [SMTP:muru_oracle_at_yahoo.com]
    Sent: Wednesday, February 14, 2001 11:16 AM
    To: Multiple recipients of list ORACLE-L
    Subject: Are snapshot logs purged?

    Hello Lists,

    I have a set of snapshots refreshing in FAST mode
    using snapshot logs. The snapshot log is keep-on
    growing as it should and after certain amount of time,
    I had to expand the tablespace space under which the
    snapshot log is located.

    My question is, Is there a way to periodically
    truncate or remove records from snapshot logs? Of
    course we can make something on our own by scheduling
    some delete jobs, but, Is there any default method
    available in Oracle?

    Any input would be highly appreciated.

    Thanks in advance,
    Muru
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author:
    INET: Scott.Shafer_at_dcpds.cpms.osd.mil

    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).
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Paul Baumgartel
    INET: PaulB_at_instipro.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).
  • James ellis at Feb 16, 2001 at 8:13 pm
    Once the fast refresh has completed the records from
    the mlog$ table should be deleted. This should happen
    automatically as long as there aren't two snapshots
    depending on the data. The high water mark on the
    table will continue to grow which will cause your
    refreshes to slow down over time. We had a job that
    ran late at night that would select from the mlog$
    table to see if there was data in it. If there wasn't
    we would truncate the mlog$ table to reset the high
    water mark.

    james
    --- Bala Muru wrote:
    Hello Lists,

    I have a set of snapshots refreshing in FAST mode
    using snapshot logs. The snapshot log is keep-on
    growing as it should and after certain amount of
    time,
    I had to expand the tablespace space under which the
    snapshot log is located.

    My question is, Is there a way to periodically
    truncate or remove records from snapshot logs? Of
    course we can make something on our own by
    scheduling
    some delete jobs, but, Is there any default method
    available in Oracle?

    Any input would be highly appreciated.

    Thanks in advance,
    Muru


    __________________________________________________
    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: Bala Muru
    INET: muru_oracle_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).
    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: james ellis
    INET: jellis24_gso_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).

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouporacle-l @
categoriesoracle
postedFeb 14, '01 at 5:20p
activeFeb 16, '01 at 8:13p
posts5
users5
websiteoracle.com

People

Translate

site design / logo © 2022 Grokbase