ORA_ROWSCN for capturing row level changes
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.
- pete91z's blog
- Log in to post comments
Comments
Interesting, but...
Why not just use Change Data Capture?
CDC is certainly on the cards
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.