Monday, August 4, 2008

Time of last DML

How to find last DML operation on table?

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

1 comment: