Re: How to find out when an Oracle table was updated the last time in Oracle 8i?
- From: Niall Litchfield <niall.litchfield@xxxxxxxxx>
- To: David Fitzjarrell <oratune@xxxxxxxxx>
- Date: Wed, 24 Apr 2013 22:43:34 +0100
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@xxxxxxxxx>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@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
>
>
>
>
--
Niall Litchfield
Oracle DBA
http://www.orawin.info
--
//www.freelists.org/webpage/oracle-l
Other related posts:
