Re: How to find out when an Oracle table was updated the last time in Oracle 8i?
- From: David Fitzjarrell <oratune@xxxxxxxxx>
- To: "tim@xxxxxxxxx" <tim@xxxxxxxxx>, Niall Litchfield <niall.litchfield@xxxxxxxxx>
- Date: Wed, 24 Apr 2013 11:42:18 -0700 (PDT)
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@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
>>
--
//www.freelists.org/webpage/oracle-l
Other related posts:
