Re: How to find out when an Oracle table was updated the last time in Oracle 8i?
Date: Wed, 24 Apr 2013 18:43:51 -0700 (PDT)
Message-ID: <1366854231.66353.YahooMailNeo_at_web121603.mail.ne1.yahoo.com>
I ran another test, this time creating the table with rowdependencies (to update the scn only for the affected row). I don't have the example handy at the moment but I also did a one minute sleep between the table creation and the insert and found that when the transaction is rolled back (and dbms_stats.flush_database_monitoring_info was executed after the insert, rollback, second insert and commit) the transaction that was rolled back did not update the timestamp but the committed transaction did. The info in DBA_TAB_MODIFICATIONS matched the ora_rowscn info. When I am at my laptop tomorrow I'll post the results.
David Fitzjarrell
From: Niall Litchfield <niall.litchfield_at_gmail.com> To: David Fitzjarrell <oratune_at_yahoo.com> Cc: "tim_at_evdbt.com" <tim_at_evdbt.com>; "oracle-l_at_freelists.org" <oracle-l_at_freelists.org> Sent: Wednesday, April 24, 2013 3:43 PM
Subject: Re: How to find out when an Oracle table was updated the last time in Oracle 8i?
We are testing (slightly) different things it appears. My test (modified from one I created no less than 8 years ago and so well worth revisiting) is as follows, db is 11.2.0.3 Linux 64bit.
SQL> connect / as sysdba
Connected.
SQL> drop user u1 cascade;
User dropped.
SQL>
SQL> create user u1 identified by u1
2 default tablespace users 3 temporary tablespace temp 4 quota unlimited on users;
User created.
SQL>
SQL> grant create session,create table,dba to u1;
Grant succeeded.
SQL> grant execute on dbms_stats to u1;
Grant succeeded.
SQL>
SQL> connect u1/u1
Connected.
SQL>
SQL> create table t1 (col1 number);
Table created.
SQL>
SQL> insert into t1
2 select rn 3 from (select rownum rn,'x' from all_objects where rownum < 1001 4 );
1000 rows created.
SQL>
SQL> rollback;
Rollback complete.
SQL>
SQL> exec dbms_stats.flush_database_monitoring_info;
PL/SQL procedure successfully completed.
SQL>
SQL> select table_name,inserts,updates,deletes from useR_tab_modifications;
TABLE_NAME INSERTS UPDATES DELETES ------------------------------ ---------- ---------- ---------- T1 1000 0 0
SQL>
SQL> spool off
I've never considered just the timestamp column, but it appears that it represents the last time data was flushed to the monitoring table, not the last dml time on the user object as the text of the docs suggests, though I guess English is sufficiently vague enough for Oracle to claim "the table" refers to the datadictionary table and not the table_name in the row. . http://docs.oracle.com/cd/E11882_01/server.112/e25513/statviews_2107.htm#i1591024 - consider my revised test below.
SQL> connect / as sysdba
Connected.
SQL> drop user u1 cascade;
User dropped.
SQL>
SQL> create user u1 identified by u1
2 default tablespace users 3 temporary tablespace temp 4 quota unlimited on users;
User created.
SQL>
SQL> grant create session,create table,dba,alter session to u1;
Grant succeeded.
SQL> grant execute on dbms_stats to u1;
Grant succeeded.
SQL>
SQL> connect u1/u1
Connected.
SQL>
SQL> create table t1 (col1 number);
Table created.
SQL>
SQL> alter session set nls_date_format='DD-MON-YYYY HH24:MI:SS';
Session altered.
SQL>
SQL> select sysdate from dual;
SYSDATE -------------------- 24-APR-2013 22:31:44
SQL>
SQL> insert into t1
2 select rn 3 from (select rownum rn,'x' from all_objects where rownum < 1001 4 );
1000 rows created.
SQL>
SQL> select sysdate from dual;
SYSDATE -------------------- 24-APR-2013 22:31:45
SQL>
SQL> rollback;
Rollback complete.
SQL>
SQL> select sysdate from dual;
SYSDATE -------------------- 24-APR-2013 22:31:45
SQL>
SQL> exec dbms_lock.sleep(30);
PL/SQL procedure successfully completed.
SQL>
SQL> exec dbms_stats.flush_database_monitoring_info;
PL/SQL procedure successfully completed.
SQL>
SQL> select table_name,inserts,timestamp from useR_tab_modifications;
TABLE_NAME INSERTS TIMESTAMP ------------------------------ ---------- -------------------- T1 1000 24-APR-2013 22:32:15
SQL>
SQL> spool off
On Wed, Apr 24, 2013 at 7:42 PM, David Fitzjarrell <oratune_at_yahoo.com> wrote:
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
>>>
>
>
-- Niall Litchfield Oracle DBA http://www.orawin.info -- http://www.freelists.org/webpage/oracle-lReceived on Thu Apr 25 2013 - 03:43:51 CEST