FAQ
One of our developers accidentally truncated a production table. That
table is infrequently updated/inserted into.



I quickly found out that flashback table does not work across truncate
commands.



Luckily I take a nightly datapump export and was able to use that to
re-populate the table.



It isn't critical that we confirm that data in the table has not changed
since the datapump export but I would like to do so. My first thought
is logminer but I can't seem to find a GUI anywhere (Grid Control or the
Java Console) like there used to be. What happened to that thing?



Is logminer that the only way to look at all the transactions where the
table in question was involved?



If so, I get it is time to read up on logminer.



Thanks!

Chris

CONFIDENTIALITY NOTICE:

This message is intended for the use of the individual or entity to which it is addressed and may contain information that is privileged,
confidential and exempt from disclosure under applicable law. If the reader of this message is not the intended recipient or the employee or agent responsible for delivering this message to the intended recipient, you are hereby notified that any dissemination, distribution or copying of this communication is strictly prohibited. If you have received this
communication in error, please notify us immediately by email reply.

Search Discussions

  • Terrian, Thomas J Mr CTR DLA J6DIB at Dec 18, 2008 at 3:55 pm
    Have you enabled table monitoring?.....if so query dba_tab_modifications
    to see if that tells you anything.

    -----Original Message-----
    From: oracle-l-bounce_at_freelists.org
    On Behalf Of Stephens, Chris
    Sent: Thursday, December 18, 2008 10:19 AM
    To: oracle-l_at_freelists.org
    Subject: best way to determine whether data in a table has changed in
    the last 24 hours

    One of our developers accidentally truncated a production table. That
    table is infrequently updated/inserted into.



    I quickly found out that flashback table does not work across truncate
    commands.



    Luckily I take a nightly datapump export and was able to use that to
    re-populate the table.



    It isn't critical that we confirm that data in the table has not changed
    since the datapump export but I would like to do so. My first thought
    is logminer but I can't seem to find a GUI anywhere (Grid Control or the
    Java Console) like there used to be. What happened to that thing?



    Is logminer that the only way to look at all the transactions where the
    table in question was involved?



    If so, I get it is time to read up on logminer.



    Thanks!

    Chris



    CONFIDENTIALITY NOTICE:

    This message is intended for the use of the individual or entity to
    which it is addressed and may contain information that is privileged,
    confidential and exempt from disclosure under applicable law. If the
    reader of this message is not the intended recipient or the employee or
    agent responsible for delivering this message to the intended recipient,
    you are hereby notified that any dissemination, distribution or copying
    of this communication is strictly prohibited. If you have received this
    communication in error, please notify us immediately by email reply.

    --
    http://www.freelists.org/webpage/oracle-l
  • Rich Jesse at Dec 18, 2008 at 4:42 pm

    Have you enabled table monitoring?.....if so query dba_tab_modifications
    to see if that tells you anything.
    Beware that the dba_tab_modifications view has limitations:

    The counts are NOT persistent across DB bounces.

    The counts are generally only since the last stats gathering, so if the
    truncate happened minutes/hours/days/weeks after the last stats were
    gathered (very likely), one can't use this view to determine activity since
    the truncate.

    The view is not updated frequently. From memory, it's somewhere near
    every 3 hours.

    Summary -- I wouldn't rely on this view in this case.

    Judging by the DP export comment from the OP, I'd have to assume that
    archivelog mode is not enabled? So I'm thinking that logminer would not be
    an option.

    If by chance archivelog mode is enabled, logminer would work, or one could
    restore backups to an alternate server/location/whatever (FOLLOWING YOUR
    SITE-SPECIFIC RECOVERY PROCEDURES of course), and recover the table data
    diffs.

    GL!

    My $.02 (adjusted for seasonal rates),
    Rich
  • Job Miller at Dec 19, 2008 at 1:20 am
    That sounds like a good chance to do a TSPITR up to point of truncate, followed by an export/import.

    I am sure you practice that regularly right?

    If not, it isn't too bad. You do have archive logs since the last backup I assume?

    Logminer has an OEM interface as well I believe (if you don't have Toad), although I haven't had the good fortune to test it out.

    Job
  • Kenneth Naim at Dec 18, 2008 at 4:22 pm
    Log miner would be the way to go. Toad has a GUI for it.



    Ken



    From: oracle-l-bounce_at_freelists.org
    On Behalf Of Stephens, Chris
    Sent: Thursday, December 18, 2008 10:19 AM
    To: oracle-l_at_freelists.org
    Subject: best way to determine whether data in a table has changed in the
    last 24 hours



    One of our developers accidentally truncated a production table. That table
    is infrequently updated/inserted into.



    I quickly found out that flashback table does not work across truncate
    commands.



    Luckily I take a nightly datapump export and was able to use that to
    re-populate the table.



    It isn't critical that we confirm that data in the table has not changed
    since the datapump export but I would like to do so. My first thought is
    logminer but I can't seem to find a GUI anywhere (Grid Control or the Java
    Console) like there used to be. What happened to that thing?



    Is logminer that the only way to look at all the transactions where the
    table in question was involved?



    If so, I get it is time to read up on logminer.



    Thanks!

    Chris



    CONFIDENTIALITY NOTICE:

    This message is intended for the use of the individual or entity to which it
    is addressed and may contain information that is privileged,
    confidential and exempt from disclosure under applicable law. If the reader
    of this message is not the intended recipient or the employee or agent
    responsible for delivering this message to the intended recipient, you are
    hereby notified that any dissemination, distribution or copying of this
    communication is strictly prohibited. If you have received this
    communication in error, please notify us immediately by email reply.

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouporacle-l @
categoriesoracle
postedDec 18, '08 at 3:19p
activeDec 19, '08 at 1:20a
posts5
users5
websiteoracle.com

People

Translate

site design / logo © 2022 Grokbase