Pseudocolumn ORA_ROWSCN

For each row, ORA_ROWSCN returns the upper system change number (SCN) of the most recent change of the row. By default, this information is kept at oracle block level, so all rows in the block will have the same ORA_ROWSCN with the updated row.
It uses SCN stored for transaction in ITL (Interesting Transaction List) from oracle block header.
To have a fine-grained view of the update SCN, this system change number can be kept at row level when row dependencies is enabled on table. This option must be enabled at table creation time with CREATE TABLE ... ROWDEPENDENCIES statement.
This pseudocolumn:

• cannot be used in a query to a view
• can be use to refer to the underlying table when creating a view
• can be use in WHERE clause of an UPDATE or DELETE statement.
• is not supported for Flashback Query. Instead of ORA_ROWSCN can  be used version query pseudocolumns. http://docs.oracle.com/cd/B19306_01/server.102/b14200/pseudocolumns003.htm#i1009358

SQL> select table_name, dependencies from user_tables;

TEST                           DISABLED
TEST_IDX                       DISABLED
-- no tables with rowdependencies enabled
SQL> desc test;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 PK_COL                                    NOT NULL NUMBER
 NOT_NULL                                  NOT NULL NUMBER
 CHECK_COL                                          NUMBER
 UK_COL                                             NUMBER
 FK_COL                                             NUMBER

SQL> select * from test;

         1          1          1          1          1
         2          2          2          2          2
         3          3          3          3          3

SQL> create table test2 as select * from test;    -- no row dependencies

Table created.

SQL> create table test3 rowdependencies as select * from test;  -- with row dependencies

Table created.


SQL> select table_name, dependencies from user_tables;

TEST3                          ENABLED
TEST2                          DISABLED
TEST                           DISABLED
TEST_IDX                       DISABLED


SQL> select ORA_ROWSCN from test2;

   9064952
   9064952
   9064952

SQL> select ORA_ROWSCN from test3;

   9065020
   9065020
   9065020

SQL> update test2 set check_col=5 where pk_col=3;

1 row updated.

SQL> commit;

Commit complete.

SQL> select ORA_ROWSCN from test2;

   9065228
   9065228
   9065228
-- same SCN for all rows in block
SQL> update test3 set check_col=5 where pk_col=3;

1 row updated.

SQL> commit;

Commit complete.

SQL> select ORA_ROWSCN from test3;

   9065020
   9065020
   9065258
-- different SCN for updated row
SQL> update test3 set check_col=4 where pk_col=3;

1 row updated.

SQL> select ORA_ROWSCN, check_col from test3;

   9065020          1
  9065020          2
                   4
-- ORA_ROWSCN is null until commit or rollback
SQL> commit;

Commit complete.

SQL> select ORA_ROWSCN, check_col from test3;

   9065020          1
  9065020          2
  9065432          4



SQL> select versions_startscn, versions_endscn,versions_xid, versions_operation, check_col from test2
  2  versions between scn minvalue and maxvalue where pk_col=3;

VERSIONS_STARTSCN VERSIONS_ENDSCN VERSIONS_XID     V  CHECK_COL
----------------- --------------- ---------------- - ----------
          9065228                 080021007E090000 U          5
                          9065228                             3

SQL> select versions_startscn, versions_endscn,versions_xid, versions_operation, check_col from test3
  2  versions between scn minvalue and maxvalue where pk_col=3;

VERSIONS_STARTSCN VERSIONS_ENDSCN VERSIONS_XID     V  CHECK_COL
----------------- --------------- ---------------- - ----------
          9065432                 090012007C090000 U          4
          9065258         9065432 020018008A090000 U          5
                          9065258                             3

SQL> -- view all versions pseudocolumns

SQL> select versions_starttime, versions_startscn, versions_endtime, versions_endscn, versions_xid, versions_operation, check_col from test2

  2  versions between scn minvalue and maxvalue where pk_col=3;

07-MAR-12 03.41.42 PM
          9065228


                080021007E090000 U          5






07-MAR-12 03.41.42 PM
        9065228                             3


SQL> select versions_starttime, versions_startscn, versions_endtime, versions_endscn, versions_xid, versions_operation, check_col from test3
  2  versions between scn minvalue and maxvalue where pk_col=3;

07-MAR-12 03.42.51 PM
          9065432


                090012007C090000 U          4


07-MAR-12 03.42.03 PM
          9065258
07-MAR-12 03.42.51 PM
        9065432 020018008A090000 U          5






07-MAR-12 03.42.03 PM


        9065258                             3


SQL>

SQL> insert into test3 values (4,4,4,4,4);

1 row created.

SQL> delete from test3 where pk_col=1;

1 row deleted.

SQL> commit;

Commit complete.

SQL> select versions_startscn, versions_endscn,versions_xid, versions_operation from test3
  2  versions between scn minvalue and maxvalue;

VERSIONS_STARTSCN VERSIONS_ENDSCN VERSIONS_XID     V
----------------- --------------- ---------------- -
          9068407                 020012008C090000 D
          9065432                 090012007C090000 U
          9065258         9065432 020018008A090000 U
                          9068407


                          9065258
          9068407                 020012008C090000 I


7 rows selected.

SQL>

where,

VERSIONS_STARTSCN - SCN of the first version of the row

VERSIONS_ENDSCN - SCN of the last version of the row

VERSIONS_XID - transaction ID 
VERSIONS_OPERATION - type of operation (I-insert, U-update, D-delete)