You could use monitoring feature that was first introduced back in Oracle 8i version but widely used in 9i version. When this feature is enabled, Oracle monitors DML changes executed on table and keeps table modification info in SGA. Every three hours or after a shutdown (for 8i version) or every 15 mins (for 9i+ version) in-memory monitoring information for all tables is flushed in the dictionary.
Oracle uses GATHER_*_STATS procedures internally to flush monitoring information. You could also execute DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO procedure to flush information manually. It is not necessary to run this procedure before gathering statistics.
After manually flushing in-memory information with DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO corresponding information in *_TAB_MODIFICAITONS views is updated immediately, without waitng for Oracle database to flush info periodically.
In 9i version tables are not implemented with monitoring by default.
SQL> select version from v$instance; VERSION ----------------- 9.2.0.6.0 1 row selected. SQL> create table test1 (a number); Table created. SQL> select monitoring from dba_tables where table_name='TEST1'; MON --- NO 1 row selected.
To enable monitoring you should execute statement : "alter table table_name monitoring".
SQL> alter table test1 monitoring; Table altered. SQL> select monitoring from dba_tables where table_name='TEST1'; MON --- YES 1 row selected.
In 10g+ version this feature is enabled by default.
SQL> select version from v$instance; VERSION ----------------- 10.2.0.4.0 1 row selected. SQL> create table test1(a number); Table created. SQL> select monitoring from dba_tables where table_name='TEST1'; MON --- YES 1 row selected.
I'll show simple example how to track dml operations using this feature.
SQL> create table test1 (id number, text varchar2(15)); Table created. SQL> select * from user_tab_modifications 2 where table_name='TEST1'; no rows selected SQL> insert into test1 values (10, 'John'); 1 row created. SQL> insert into test1 values (11, 'Marie'); 1 row created. SQL> insert into test1 values (13, 'Katie'); 1 row created. SQL> insert into test1 values (14, 'James'); 1 row created. SQL> commit; Commit complete. SQL> select * from user_tab_modifications 2 where table_name='TEST1'; no rows selected SQL> exec dbms_stats.flush_database_monitoring_info; PL/SQL procedure successfully completed. SQL> select table_name, inserts, updates, deletes, timestamp,truncated 2 from user_tab_modifications 3 where table_name='TEST1'; TABLE_NAME INSERTS UPDATE DELETES TIMESTAMP TRU DROP_SEG --------- -------- ------- -------- ------------------- --- -------- TEST1 4 0 0 04.08.2008 12:03:32 NO 0 1 row selected. SQL> update test1 set text='Marianne' where id=11; 1 row updated. SQL> commit; Commit complete. SQL> exec dbms_stats.flush_database_monitoring_info; PL/SQL procedure successfully completed. SQL> select table_name, inserts, updates, deletes, timestamp,truncated 2 from user_tab_modifications 3 where table_name='TEST1'; TABLE_NAME INSERTS UPDATES DELETES TIMESTAMP TRU ------------- ---------- ---------- ---------- ------------------- --- TEST1 4 1 0 04.08.2008 12:04:50 NO 1 row selected.
From this view you can see when was last DML executed on specified table.
Another way of finding information about last DML's is using ORA_ROWSCN pseudocolumn. This column is available on 10g+ database versions. Using ora_rowscn to find last DML is easier then using previous method that I mentioned because we don't have to think about gathering table statistics and dumping information to dictionary.
From documentation:
ORA_ROWSCN is a pseudocolumn of any table that is not fixed or external. It represents the SCN of the most recent change to a given row; that is, the latest COMMIT operation for the row. To convert an SCN to the corresponding TIMESTAMP value, use the function SCN_TO_TIMESTAMP.
ORA_ROWSCN is a conservative upper bound of the latest commit time—the actual commit SCN can be somewhat earlier. ORA_ROWSCN is more precise (closer to the actual commit SCN) for a row-dependent table (created using CREATE TABLE with the ROWDEPENDENCIES clause).
This pseudocolumn is useful for determinig APPROXIMATELY when a row was last updated - it is NOT absolutely precise!
Small test:
SQL> create table test1 (id number, name varchar2(20)); Table created. SQL> insert into test1 select empno, ename from scott.emp; 14 rows created. SQL> commit; Commit complete. SQL> select ora_rowscn, scn_to_timestamp(ora_rowscn), t.* 2 from test1 t; ORA_ROWSCN SCN_TO_TIMESTAMP(ORA_ROWSCN) ID NAME ---------- --------------------------- ------- -------------------- 4478907023 04.08.08 13:19:21.000000000 7369 SMITH 4478907023 04.08.08 13:19:21.000000000 7499 ALLEN 4478907023 04.08.08 13:19:21.000000000 7521 WARD 4478907023 04.08.08 13:19:21.000000000 7566 JONES 4478907023 04.08.08 13:19:21.000000000 7654 MARTIN 4478907023 04.08.08 13:19:21.000000000 7698 BLAKE 4478907023 04.08.08 13:19:21.000000000 7782 CLARK 4478907023 04.08.08 13:19:21.000000000 7788 SCOTT 4478907023 04.08.08 13:19:21.000000000 7839 KING 4478907023 04.08.08 13:19:21.000000000 7844 TURNER 4478907023 04.08.08 13:19:21.000000000 7876 ADAMS 4478907023 04.08.08 13:19:21.000000000 7900 JAMES 4478907023 04.08.08 13:19:21.000000000 7902 FORD 4478907023 04.08.08 13:19:21.000000000 7934 MILLER 14 rows selected. SQL> update test1 set name = 'MARSHALL' where id = 7369; 1 row updated. SQL> commit; Commit complete. SQL> select ora_rowscn, scn_to_timestamp(ora_rowscn), t.* 2 from test1 t; ORA_ROWSCN SCN_TO_TIMESTAMP(ORA_ROWSCN) ID NAME ---------- ---------------------------- ------ ------ 4478907073 04.08.08 13:21:38.000000000 7369 MARSHALL <--- 4478907073 04.08.08 13:21:38.000000000 7499 ALLEN 4478907073 04.08.08 13:21:38.000000000 7521 WARD 4478907073 04.08.08 13:21:38.000000000 7566 JONES 4478907073 04.08.08 13:21:38.000000000 7654 MARTIN 4478907073 04.08.08 13:21:38.000000000 7698 BLAKE 4478907073 04.08.08 13:21:38.000000000 7782 CLARK 4478907073 04.08.08 13:21:38.000000000 7788 SCOTT 4478907073 04.08.08 13:21:38.000000000 7839 KING 4478907073 04.08.08 13:21:38.000000000 7844 TURNER 4478907073 04.08.08 13:21:38.000000000 7876 ADAMS 4478907073 04.08.08 13:21:38.000000000 7900 JAMES 4478907073 04.08.08 13:21:38.000000000 7902 FORD 4478907073 04.08.08 13:21:38.000000000 7934 MILLER 14 rows selected.I've updated just one row but timestamp has changed for whole table. After every commit Oracle updates SCN which is coded in the block's header - so every row from the same block gets newest SCN number. But still, I often want to know when last DML happened on table and for this purpose ora_rowscn is very precise and useful.
SQL> select max(scn_to_timestamp(ora_rowscn)) 2 from TEST1; MAX(SCN_TO_TIMESTAMP(ORA_ROWSCN)) --------------------------------------------------------------------------- 04.08.08 13:21:38.000000000 1 row selected.From documentation:
You can obtain a more fine-grained approximation of the SCN by creating your tables with row-level dependency tracking. Refer to CREATE TABLE ... NOROWDEPENDENCIES | ROWDEPENDENCIES for more information on row-level dependency tracking.
SQL> create table test2 (id number, name varchar2(20)) rowdependencies; Table created. SQL> insert into test2 select empno,ename from scott.emp; 14 rows created. SQL> commit; Commit complete. SQL> select ora_rowscn, scn_to_timestamp(ora_rowscn), t.* from test2 t; ORA_ROWSCN SCN_TO_TIMESTAMP(ORA_ROWSCN) ID NAME ---------- ---------------- ---------- ---------- -------- 4478907943 04.08.08 13:50:09.000000000 7369 SMITH 4478907943 04.08.08 13:50:09.000000000 7499 ALLEN 4478907943 04.08.08 13:50:09.000000000 7521 WARD 4478907943 04.08.08 13:50:09.000000000 7566 JONES 4478907943 04.08.08 13:50:09.000000000 7654 MARTIN 4478907943 04.08.08 13:50:09.000000000 7698 BLAKE 4478907943 04.08.08 13:50:09.000000000 7782 CLARK 4478907943 04.08.08 13:50:09.000000000 7788 SCOTT 4478907943 04.08.08 13:50:09.000000000 7839 KING 4478907943 04.08.08 13:50:09.000000000 7844 TURNER 4478907943 04.08.08 13:50:09.000000000 7876 ADAMS 4478907943 04.08.08 13:50:09.000000000 7900 JAMES 4478907943 04.08.08 13:50:09.000000000 7902 FORD 4478907943 04.08.08 13:50:09.000000000 7934 MILLER 14 rows selected. SQL> update test2 set name='MARSHALL' where id=7369 2 ; 1 row updated. SQL> commit; Commit complete. SQL> select ora_rowscn, scn_to_timestamp(ora_rowscn), t.* from test2 t; ORA_ROWSCN SCN_TO_TIMESTAMP(ORA_ROWSCN) ID NAME ---------- ---------------------------- ----- -------------------- 4478907997 04.08.08 13:51:03.000000000 7369 MARSHALL <---- 4478907943 04.08.08 13:50:09.000000000 7499 ALLEN 4478907943 04.08.08 13:50:09.000000000 7521 WARD 4478907943 04.08.08 13:50:09.000000000 7566 JONES 4478907943 04.08.08 13:50:09.000000000 7654 MARTIN 4478907943 04.08.08 13:50:09.000000000 7698 BLAKE 4478907943 04.08.08 13:50:09.000000000 7782 CLARK 4478907943 04.08.08 13:50:09.000000000 7788 SCOTT 4478907943 04.08.08 13:50:09.000000000 7839 KING 4478907943 04.08.08 13:50:09.000000000 7844 TURNER 4478907943 04.08.08 13:50:09.000000000 7876 ADAMS 4478907943 04.08.08 13:50:09.000000000 7900 JAMES 4478907943 04.08.08 13:50:09.000000000 7902 FORD 4478907943 04.08.08 13:50:09.000000000 7934 MILLER 14 rows selected.From documentation: Using ROWDEPENDENCIES table will use row-level dependency tracking. With this feature, each row in the table has a system change number (SCN) that represents a time greater than or equal to the commit time of the last transaction that modified the row. You cannot change this setting after table is created. RELATED DOCUMENTS: DBMS_STATS ORA_ROWSCN Pseudocolumn
Good Post..!!
ReplyDelete