Re: How to find out when an Oracle table was updated the last time in Oracle 8i?
Date: Wed, 24 Apr 2013 12:47:46 -0700 (PDT)
Message-ID: <1366832866.56565.YahooMailNeo_at_web121604.mail.ne1.yahoo.com>
I responded to this before.
David Fitzjarrell
From: "Mandal, Ashoke" <ashoke.k.mandal_at_medtronic.com> To: "oratune_at_yahoo.com" <oratune_at_yahoo.com>; "tim_at_evdbt.com" <tim_at_evdbt.com>; Niall Litchfield <niall.litchfield_at_gmail.com> Cc: "oracle-l_at_freelists.org" <oracle-l_at_freelists.org> Sent: Wednesday, April 24, 2013 1:02 PM
Subject: RE: How to find out when an Oracle table was updated the last time in Oracle 8i?
Hello all,
The SCN_TO_TIMESTAMP(MAX(ora_rowscn)) works from 10g database but I am looking for help how to do this in Oracle 8i database.
Thanks,
Ashoke
-----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of David Fitzjarrell
Sent: Wednesday, April 24, 2013 1:42 PM
To: tim_at_evdbt.com; Niall Litchfield
Cc: oracle-l_at_freelists.org
Subject: Re: How to find out when an Oracle table was updated the last time in Oracle 8i?
Unless this has been changed in 11.2 it appears to report the committed changes:
SQL> SELECT SCN_TO_TIMESTAMP(MAX(ora_rowscn)) from emp;
SCN_TO_TIMESTAMP(MAX(ORA_ROWSCN))
24-APR-13 12.39.08.000000000 PM
SQL>
SQL> exec dbms_stats.flush_database_monitoring_info
PL/SQL procedure successfully completed.
SQL> SQL> select table_owner, table_name, to_char(timestamp, 'DD-MM-RRRR SQL> HH24:MI:SS') timestamp
2 from dba_tab_modifications
3 where table_name = 'EMP';
]TABLE_OWNER TABLE_NAME TIMESTAMP ------------------------------ ------------ ------------------- BING EMP 24-04-2013 12:39:09
SQL>
SQL> insert into emp
2 values (8000, 'FNARM', 'PICKLER', 7734, sysdate-43, 3000, 0, 20);
1 row created.
SQL>
SQL> SELECT SCN_TO_TIMESTAMP(MAX(ora_rowscn)) from emp;
SCN_TO_TIMESTAMP(MAX(ORA_ROWSCN))
24-APR-13 12.39.08.000000000 PM
SQL>
SQL> exec dbms_stats.flush_database_monitoring_info
PL/SQL procedure successfully completed.
SQL> SQL> select table_owner, table_name, to_char(timestamp, 'DD-MM-RRRR SQL> HH24:MI:SS') timestamp
2 from dba_tab_modifications
3 where table_name = 'EMP';
TABLE_OWNER TABLE_NAME TIMESTAMP ------------------------------ ------------ ------------------- BING EMP 24-04-2013 12:39:09
SQL>
SQL> rollback;
Rollback complete.
SQL>
SQL> SELECT SCN_TO_TIMESTAMP(MAX(ora_rowscn)) from emp;
SCN_TO_TIMESTAMP(MAX(ORA_ROWSCN))
24-APR-13 12.39.08.000000000 PM
SQL>
SQL> exec dbms_stats.flush_database_monitoring_info
PL/SQL procedure successfully completed.
SQL> SQL> select table_owner, table_name, to_char(timestamp, 'DD-MM-RRRR SQL> HH24:MI:SS') timestamp
2 from dba_tab_modifications
3 where table_name = 'EMP';
TABLE_OWNER TABLE_NAME TIMESTAMP ------------------------------ ------------ ------------------- BING EMP 24-04-2013 12:39:09
SQL>
Of course I've been known to be wrong.
David Fitzjarrell
From: Tim Gorman <tim_at_evdbt.com>
To: Niall Litchfield <niall.litchfield_at_gmail.com> Cc: David Fitzjarrell <oratune_at_yahoo.com>; "oracle-l_at_freelists.org" <oracle-l_at_freelists.org> Sent: Wednesday, April 24, 2013 10:20 AM Subject: Re: How to find out when an Oracle table was updated the last time in Oracle 8i?
Log Miner data also contains rolled back data, but there is a way to identify that.
On 4/24/2013 10:10 AM, Niall Litchfield wrote:
That's a nice idea. It does have a couple of caveats though. First, the information is only flushed every few (I think 3) hours so it will only be an approximate measure, and second dba_tab_modifications includes transactions that rolled back. I imagine that actually what the original poster requires is auditing.
> > > > > >On Wed, Apr 24, 2013 at 3:56 PM, David Fitzjarrell <oratune_at_yahoo.com> wrote: > >If tables are monitored (which is available in 8i) you can also query DBA_TAB_MODIFICATIONS: >> >>set linesize 150 >> >>select table_owner, table_name, partition_name, subpartition_name, >>to_char(timestamp, 'DD-MM-YYYY HH24:MI:SS') last_mod from >>dba_tab_modifications order by 3; >> >>You can give this a try. >> >>David Fitzjarrell >> >> >> >>________________________________ >>From: Tim Gorman <tim_at_evdbt.com> >>To: oracle-l_at_freelists.org >>Sent: Tuesday, April 23, 2013 9:27 PM >>Subject: Re: How to find out when an Oracle table was updated the last time in Oracle 8i? >> >> >> >>Ashoke, >> >>DBMS_LOGMNR was available in Oracle8i; you can mine the redo logs for >>that information. >> >>Hope this helps... >> >>-Tim >> >> >>On 4/23/2013 3:54 PM, Mandal, Ashoke wrote: >>> Hello All, >>> >>> Could you please tell me how to find out when an Oracle table was >>> updated the last time in Oracle 8i. In Oracle 10g you could use the >>> following query >>> SQL> SELECT SCN_TO_TIMESTAMP(MAX(ora_rowscn)) from <table_name>; but this doesn't work in Oracle 8i. >>> >>> Any help will be appreciated. >>> >>> Thanks, >>> Ashoke >>
-- http://www.freelists.org/webpage/oracle-l [CONFIDENTIALITY AND PRIVACY NOTICE] Information transmitted by this email is proprietary to Medtronic and is intended for use only by the individual or entity to which it is addressed, and may contain information that is private, privileged, confidential or exempt from disclosure under applicable law. If you are not the intended recipient or it appears that this mail has been forwarded to you without proper authority, you are notified that any use or dissemination of this information in any manner is strictly prohibited. In such cases, please delete this mail from your records. To view this notice in other languages you can either select the following link or manually copy and paste the link into the address bar of a web browser: http://emaildisclaimer.medtronic.com/ -- http://www.freelists.org/webpage/oracle-l -- http://www.freelists.org/webpage/oracle-lReceived on Wed Apr 24 2013 - 21:47:46 CEST