Results 1 to 9 of 9
  1. #1
    Join Date
    Feb 2004
    Posts
    20

    Unanswered: How can I tell when a table has been update

    Hi,

    All tables in our database have been emptied and was wondering if there is a way to see when it was last updated (DML not DDL)?

    Thanks.

  2. #2
    Join Date
    May 2004
    Location
    Dominican Republic
    Posts
    721
    Suppose you know when was it -- then what ?

    If you dont have auditing enable, in 10g, having a nice undo_retention, you could run a query like
    Code:
    select versions_starttime, versions_endtime, versions_xid, versions_operation
      from your_table versions between timestamp minvalue and maxvalue
     order by versions_starttime
    and versions_operation will tell you which DML operation ocurred (I for insert, U for update and D for delete). If you want to find out the offending SQL statement, you could
    Code:
    select undo_sql
      from FLASHBACK_TRANSACTION_QUERY
     where xid = <version_xid from the query above>

  3. #3
    Join Date
    Feb 2004
    Posts
    20
    Wow, that was quick. Thanks. Unfortunately the first query returned no rows.
    Last edited by danielbrowne; 11-21-06 at 11:37.

  4. #4
    Join Date
    Feb 2004
    Posts
    20
    I should have added that the version we are using is:

    Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64bit Production

  5. #5
    Join Date
    Feb 2004
    Posts
    20
    It would be great if your query worked and you've read my mind about getting the offending SQL statement that caused the deletes.

  6. #6
    Join Date
    Feb 2004
    Posts
    20
    Running the following with different timesstamps has given me the rough time that rows were deleted:


    SQL> select count(*) from apf_Action_type AS OF TIMESTAMP TO_DATE('21-Nov-06 11:59','DD-Mon-YY HH24:MI')

    COUNT(*)
    ----------
    20

    SQL> select count(*) from apf_Action_type AS OF TIMESTAMP TO_DATE('21-Nov-06 12:00','DD-Mon-YY HH24:MI');

    COUNT(*)
    ----------
    0

  7. #7
    Join Date
    May 2004
    Location
    Dominican Republic
    Posts
    721
    Well, that just tell you the undo generated for the delete that caused your tables to be empty was overwritten (it was expected I believe given all tables were deleted) and that probably retention guarantee was not set for the undo tablespace used to hold the undo information of the delete.

    There's litle you can do about it (like, check v$sql for delete statements, hope you're lucky). I would suggest you to restore the db to a previous point in time and enable auditing to actually (auditing is enable by default on 10g but only for user SYS) find the culript of this.

    If hope somebody else has other suggestion about this for you.

  8. #8
    Join Date
    Feb 2004
    Posts
    20
    Thanks for you help.

  9. #9
    Join Date
    Nov 2006
    Location
    Indianapolis
    Posts
    90
    try logminer... though it may not be realistic if the window of data loss is large, or if you've got too many archive logs to spin through

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •