Re: How to find out when an Oracle table was updated the last time in Oracle 8i?

From: David Fitzjarrell <oratune_at_yahoo.com>
Date: Wed, 24 Apr 2013 11:42:18 -0700 (PDT)
Message-ID: <1366828938.51323.YahooMailNeo_at_web121604.mail.ne1.yahoo.com>

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 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 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 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

Received on Wed Apr 24 2013 - 20:42:18 CEST