RE: How to find out when an Oracle table was updated the last time in Oracle 8i?
- From: "Mandal, Ashoke" <ashoke.k.mandal@xxxxxxxxxxxxx>
- To: "oratune@xxxxxxxxx" <oratune@xxxxxxxxx>, "tim@xxxxxxxxx" <tim@xxxxxxxxx>, Niall Litchfield <niall.litchfield@xxxxxxxxx>
- Date: Wed, 24 Apr 2013 19:02:44 +0000
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
>>
--
//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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
