FAQ
All,



I have a table with MV logs in a source database. Two other databases
have created MV's based on this table. Is there some way to determine
which database needs to refresh their MV so that all of the MV logs will
be cleared?

Thanks

Tom

This transmission may contain confidential, proprietary, or privileged information which is intended solely for use by the individual or entity to whom it is addressed. If you are not the intended recipient, you are hereby notified that any disclosure, dissemination, copying or distribution of this transmission or its attachments is strictly prohibited. In addition, unauthorized access to this transmission may violate federal or State law, including the Electronic Communications Privacy Act of 1985. If you have received this transmission in error, please notify the sender immediately by return e-mail and delete the transmission and its attachments.

Search Discussions

  • Thomas Day at Jan 19, 2007 at 4:27 pm
    Here's a frangment of something, hope it helps -

    select snapid, r.name snapname, '||

    ' nvl(r.snapshot_site, ''not registered'') snapsite, ' ||
    ' snaptime ' ||
    'from sys.slog$ s, dba_registered_snapshots r ' ||
    'where s.snapid=r.snapshot_id(+)

    On 1/19/07, Mercadante, Thomas F (LABOR) <
    Thomas.Mercadante_at_labor.state.ny.us> wrote:
    All,



    I have a table with MV logs in a source database. Two other databases
    have created MV's based on this table. Is there some way to determine which
    database needs to refresh their MV so that all of the MV logs will be
    cleared?


    Thanks


    Tom



    ------------------------------

    This transmission may contain confidential, proprietary, or privileged
    information which is intended solely for use by the individual or entity to
    whom it is addressed. If you are not the intended recipient, you are
    hereby notified that any disclosure, dissemination, copying or distribution
    of this transmission or its attachments is strictly prohibited. In
    addition, unauthorized access to this transmission may violate federal or
    State law, including the Electronic Communications Privacy Act of 1985. If
    you have received this transmission in error, please notify the sender
    immediately by return e-mail and delete the transmission and its
    attachments.


    --
    http://www.freelists.org/webpage/oracle-l
  • Hemant K Chitale at Jan 19, 2007 at 4:27 pm
    See Note # 1031924.6
    You should be able to "reverse-engineer" from there using SYS.SLOG$
    SNAPID and SNAPTIME against
    MLOG$_

    Hemant
    At 10:50 PM Friday, Mercadante, Thomas F \(LABOR\) wrote:

    All,

    I have a table with MV logs in a source database. Two other
    databases have created MV's based on this table. Is there some way
    to determine which database needs to refresh their MV so that all of
    the MV logs will be cleared?
    Hemant K Chitale
    http://web.singnet.com.sg/~hkchital
    and
    http://hemantoracledba.blogspot.com
  • Thomas Day at Jan 19, 2007 at 4:47 pm
    Here's a cript that I used to use to monitor my snapshot refreshes.

    On 1/19/07, Mercadante, Thomas F (LABOR) <
    Thomas.Mercadante_at_labor.state.ny.us> wrote:
    All,



    I have a table with MV logs in a source database. Two other databases
    have created MV's based on this table. Is there some way to determine which
    database needs to refresh their MV so that all of the MV logs will be
    cleared?


    Thanks


    Tom



    ------------------------------

    This transmission may contain confidential, proprietary, or privileged
    information which is intended solely for use by the individual or entity to
    whom it is addressed. If you are not the intended recipient, you are
    hereby notified that any disclosure, dissemination, copying or distribution
    of this transmission or its attachments is strictly prohibited. In
    addition, unauthorized access to this transmission may violate federal or
    State law, including the Electronic Communications Privacy Act of 1985. If
    you have received this transmission in error, please notify the sender
    immediately by return e-mail and delete the transmission and its
    attachments.


    --
    http://www.freelists.org/webpage/oracle-l

    text/plain attachment: qmlogs.sql
  • Mercadante, Thomas F \(LABOR\) at Jan 19, 2007 at 7:59 pm
    All,



    All of these replies were perfect! I got what I wanted from you all and
    fashioned a report to run to help us understand what MV's need
    refreshing.

    Thanks again

    Tom



    This transmission may contain confidential, proprietary, or privileged information which is intended solely for use by the individual or entity to whom it is addressed. If you are not the intended recipient, you are hereby notified that any disclosure, dissemination, copying or distribution of this transmission or its attachments is strictly prohibited. In addition, unauthorized access to this transmission may violate federal or State law, including the Electronic Communications Privacy Act of 1985. If you have received this transmission in error, please notify the sender immediately by return e-mail and delete the transmission and its attachments.

    From: Jared Still
    Sent: Friday, January 19, 2007 1:06 PM
    To: Mercadante, Thomas F (LABOR)
    Subject: Re: Materialized View Needing to be Refreshed





    On 1/19/07, Mercadante, Thomas F (LABOR)
    wrote:

    I have a table with MV logs in a source database. Two other databases
    have created MV's based on this table. Is there some way to determine
    which database needs to refresh their MV so that all of the MV logs will
    be cleared?

    Hi Tom,

    The keys to determining which snapshots need refreshed are found in the
    DBA_SNAPSHOT_LOGS view on the destination site, and the sys.snap$
    table on the source sites.

    The DBA_SNAPSHOT_LOGS view contains the SNAPSHOT_ID column,
    which you won't find anywhere else.

    On the destination site:

    select

    log_owner,master,log_table,snapshot_id,to_char(current_snapshots,'mm/dd/yyyy hh24:mi:ss') current_snapshots

    from dba_snapshot_logs
    order by 1

    On the source sites, this will get the snap_id the MV's:

    select sowner,vname,tname, snapid
    from sys.snap$
    /

    This is easiest to test by creating a table and 2 MV's on a single
    database, and running these queries.

    Attached are some notes I made last year while playing with MV's.

    HTH,



    --

    Jared Still
    Certifiable Oracle DBA and Part Time Perl Evangelist

    --
    http://www.freelists.org/webpage/oracle-l

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouporacle-l @
categoriesoracle
postedJan 19, '07 at 2:50p
activeJan 19, '07 at 7:59p
posts5
users3
websiteoracle.com

People

Translate

site design / logo © 2022 Grokbase