ORA_ROWSCN for capturing row level changes

articles: 

I've been working on way to capture changed rows using ora_rowscn for part of an ETL routine. Here are some details of what I've tested and found.

By default, the ora_rowscn pseudo column reports the scn at block level. This means that rows sharing the same block will have the same ora_rowscn, and if one row were to be changed, then scn would be altered for both blocks.

For example:

create table carpentp.scn_test
(first_column number, second_column varchar2(20));

insert into carpentp.scn_test values (1,'First value in table');
insert into carpentp.scn_test values (2,'2nd value in table');

select first_column,second_column,ora_rowscn from carpentp.scn_test;

FIRST_COLUMN SECOND_COLUMN        ORA_ROWSCN
------------ -------------------- ----------
           1 First value in table 4054738639
           2 2nd value in table   4054738639

update carpentp.scn_test set second_column='Changed 2nd column' where first_column=2;

1 row updated

SQL> commit;

Commit complete

SQL> select first_column,second_column,ora_rowscn from carpentp.scn_test;

FIRST_COLUMN SECOND_COLUMN        ORA_ROWSCN
------------ -------------------- ----------
           1 First value in table 4054741465
           2 Changed 2nd column   4054741465

This appears to be further complicated when the table is partitioned:

-- Create table
create table CARPENTP_PARTITION_TEST
(
  ORDERID       NUMBER(10) not null,
  CUSTOMERID    NUMBER(10) not null,
  PRODUCTID     NUMBER(10) not null,
  STATUS        VARCHAR2(100)
)
partition by list (STATUS)
(
  partition P_1 values ('POSTED', 'CANCELLED')
    tablespace users
    pctfree 10
    initrans 1
    maxtrans 255
    storage
    (
      initial 306240K
      minextents 1
      maxextents unlimited
    ),
  partition P_2 values ('PRINTING', 'PICKING')
    tablespace PLAYWEBORDER
    pctfree 10
    initrans 1
    maxtrans 255
    storage
    (
      initial 306240K
      minextents 1
      maxextents unlimited
    )
)
;
SQL> alter table carpentp_partition_test enable row movement;

Table altered.

SQL> insert into carpentp_partition_test values (1000,1000,1000,'POSTED');

1 row inserted

SQL> insert into carpentp_partition_test values (2000,2000,2000,'POSTED');

1 row inserted

SQL> commit;

Commit complete

SQL> insert into carpentp_partition_test values (3000,3000,3000,'PRINTING');

1 row inserted

SQL> insert into carpentp_partition_test values (4000,4000,4000,'PICKING');

1 row inserted

SQL> commit;

Commit complete

SQL> select orderid,customerid,productid,status,ora_rowscn, dbms_rowid.rowid_block_number(rowid) as block_no from carpentp_partition_test;

 ORDERID CUSTOMERID PRODUCTID USTATUS           ORA_ROWSCN   BLOCK_NO
-------- ---------- --------- ----------------- ---------- ----------
    1000       1000      1000 POSTED            3162216703        682
    2000       2000      2000 POSTED            3162216703        682
    3000       3000      3000 PRINTING          3162216822    1068842
    4000       4000      4000 PICKING           3162216822    1068842

 Indicates data is spread over 2 blocks.

SQL> insert into carpentp_partition_test values (5000,5000,5000,'POSTED');

1 row inserted

SQL> insert into carpentp_partition_test values (6000,6000,6000,'PRINTING');
	
1 row inserted

SQL> commit;

Commit complete

SQL> select orderid,customerid,productid,status,ora_rowscn, dbms_rowid.rowid_block_number(rowid) as block_no from carpentp_partition_test;

 ORDERID CUSTOMERID PRODUCTID STATUS           ORA_ROWSCN   BLOCK_NO
-------- ---------- --------- ---------------- ---------- ----------
    1000       1000      1000 POSTED           3162217117        682
    2000       2000      2000 POSTED           3162217117        682
    5000       5000      5000 POSTED           3162217117        682
    3000       3000      3000 PRINTING         3162217117    1068842
    4000       4000      4000 PICKING          3162217117    1068842
    6000       6000      6000 PRINTING         3162217117    1068842

 Indicates both blocks were changed in the same transaction

SQL> update carpentp_partition_test set nvcustatus='POSTED' where intorderid=6000;

1 row updated

SQL> commit;

Commit complete

SQL> select orderid,customerid,productid,status,ora_rowscn, dbms_rowid.rowid_block_number(rowid) as block_no from carpentp_partition_test;

 ORDERID CUSTOMERID PRODUCTID STATUS           ORA_ROWSCN   BLOCK_NO
-------- ---------- --------- ---------------- ---------- ----------
    1000       1000      1000 POSTED           3162217218        682
    2000       2000      2000 POSTED           3162217218        682
    5000       5000      5000 POSTED           3162217218        682
    6000       6000      6000 POSTED           3162217218        682
    3000       3000      3000 PRINTING         3162217218    1068842
    4000       4000      4000 PICKING          3162217218    1068842

 Indicates that the row movement from partition 2 to partition 1 has caused the ora_rowscn to change on both blocks

The conclusion therefore is that block level ora_rowscn would never be suitable for collecting row level data. Our options at this stage then are to either rebuild the tables using rowdependencies e.g.

SQL> create table phc_test (first_column number, second_column varchar2(20)) rowdependencies;

Table created

SQL> insert into phc_test values (1,'first value');

1 row inserted

SQL> insert into phc_test values (2,'second value');

1 row inserted

SQL> commit;

Commit complete

SQL> insert into phc_test values (3,'third value');

1 row inserted

SQL> insert into phc_test values (4,'fourth value');

1 row inserted

SQL> commit;

Commit complete

SQL> select rowid,first_column,second_column,ora_rowscn from phc_test;

ROWID              FIRST_COLUMN SECOND_COLUMN        ORA_ROWSCN
------------------ ------------ -------------------- ----------
AAAVicAAEAAAXUvAAA            1 first value          3162214179
AAAVicAAEAAAXUvAAB            2 second value         3162214179
AAAVicAAEAAAXUvAAC            3 third value          3162214274
AAAVicAAEAAAXUvAAD            4 fourth value         3162214274

SQL> select first_column, second_column,ora_rowscn, dbms_rowid.rowid_block_number(rowid) as block_no from phc_test;

FIRST_COLUMN SECOND_COLUMN        ORA_ROWSCN   BLOCK_NO
------------ -------------------- ---------- ----------
           1 first value          3162214179      95535
           2 second value         3162214179      95535
           3 third value          3162214274      95535
           4 fourth value         3162214274      95535

 Query shows that all rows are in the same block but have different ora_rowscn values. This holds true for row movement in partitioned tables a well – the ora_rowscn is only changed for the row that moved to a different partition.

However, rebuilding a central order table for example is no small task, you would have to arrange for downtime, check object dependencies, check space requirements (rowdependencies requires an extra 6 bytes per row), etc.

If space is not a problem, I have found a workaround which requires no downtime using materialized views.

First off, you create a materialized view log on the original table and a new table using rowdependencies with the columns you require from original table.

SQL> create materialized view log on phc_test;

create table phc_test_mv (first_column number, second_column varchar2(20)) rowdependencies;

You then populate the new table from the old one:

insert into phc_test_mv select * from phc_test;

Finally create a materialized view on top of the new table:

create materializied view on prebuilt table phc_test_mv
refresh fast on demand
as select * from phc_test;

You can either use a regularly scheduled dba_job or manual refresh using dbms_mview.refresh to update the materialized view periodically. In testing, this has proven to successfully isolate changes down to row level, thus enabling us to capture changed rows within a given timeframe using functions such as timestamp_to_scn. e.g.

select * from phctest_mv where ora_rowscn >=timestamp_to_scn(sysdate-30/1440);

Of course, this uses elements of flashback and is tied to your undo_retention and undo tablespace so your earliest timestamp_to_scn possibility will depend on how large your retention period is. In this case, ours is set to 24 hours and I have created a crontab job to capture changes every 2 hours.

Comments

Why not just use Change Data Capture?

CDC is certainly on the cards as a replacement - the trouble is we have existing ETL routines that were written by a third party company which (badly) use ora_rowscn on tables that were built without rowdependencies. Initially I was looking at a way to reduce the number of rows fetched when using ora_rowscn, but without having to change the base tables.