determine the latest COMMIT operation for the row

来源：互联网发布：淘宝一周流量分布图编辑：程序博客网时间：2024/06/05 12:14

In 10g there is a new 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. For example:

SELECT ora_rowscn, last_name, salary 
FROM employees 
WHERE employee_id = 7788;

ORA_ROWSCN    NAME    SALARY
----------    ----    ------
    202553    Fudd      3000

The latest COMMIT operation for the row took place at approximately SCN 202553. You can use function SCN_TO_TIMESTAMP to convert a SCN, like ORA_ROWSCN, to the corresponding TIMESTAMP value.

SQL> create table table_test(id number, name varchar2(50));

Table created.

SQL> set time on
16:26:06 SQL> insert into table_test values (1, 'name1');

1 row created.

16:26:27 SQL> commit;

Commit complete.

16:26:30 SQL> insert into table_test values (2, 'name2');

1 row created.

16:26:37 SQL> commit;

Commit complete.

16:26:39 SQL> insert into table_test values (3, 'name3');

1 row created.

16:26:47 SQL> commit;

Commit complete.

16:26:48 SQL> select max(scn_to_timestamp(ora_rowscn)) from table_test;

MAX(SCN_TO_TIMESTAMP(ORA_ROWSCN))
---------------------------------------------------------------------------
23-OCT-08 04.26.47.000000000 PM

16:27:38 SQL>