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@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On 
Behalf Of David Fitzjarrell
Sent: Wednesday, April 24, 2013 1:42 PM
To: tim@xxxxxxxxx; Niall Litchfield
Cc: oracle-l@xxxxxxxxxxxxx
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@xxxxxxxxx>
To: Niall Litchfield <niall.litchfield@xxxxxxxxx>
Cc: David Fitzjarrell <oratune@xxxxxxxxx>; "oracle-l@xxxxxxxxxxxxx" 
<oracle-l@xxxxxxxxxxxxx>
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@xxxxxxxxx> 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@xxxxxxxxx>
>>To: oracle-l@xxxxxxxxxxxxx
>>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


Other related posts: