Find out if two rows came from same transaction in Oracle

Question

I am using Oracle 11.1 and would like to figure out if two rows in one table where inserted by the same transaction.

Is there an easy sql statement to map a row to an transactions id? The table is an insert only table.

What are my options? Some baisc solution would do, I don't really want some extra tool to analyze the log files.

Thanks

Jack Douglas · Accepted Answer · 2011-11-26T13:06:58.063

You can get the SCN for a row with the ORA_ROWSCN pseudocolumn

Unless you have set row-level dependency tracking for the table, this will report the SCN of the last change to the block the row is in, which may not be much use. You can turn on row-level dependency tracking at create table time only, so you may need to drop and re-create your table.

Note that row-level tracking increases the size of each row by 6 bytes.

with rowdependencies:

create table foo rowdependencies as
select level as id from dual connect by level<=10000;

select count(distinct ora_rowscn) from foo;

COUNT(DISTINCTORA_ROWSCN) 
------------------------- 
1                         

begin
  for r in (select id from foo) loop
    update foo set id = id where id=r.id;
    commit;
  end loop;
end;
/

select count(distinct ora_rowscn) from foo;

COUNT(DISTINCTORA_ROWSCN) 
------------------------- 
10000

without rowdependencies (norowdependencies is the default):

create table bar as
select level as id from dual connect by level<=10000;

select count(distinct ora_rowscn) from bar;

COUNT(DISTINCTORA_ROWSCN) 
------------------------- 
1                         

begin
  for r in (select id from bar) loop
    update bar set id = id where id=r.id;
    commit;
  end loop;
end;
/

select count(distinct ora_rowscn) from bar;

COUNT(DISTINCTORA_ROWSCN) 
------------------------- 
16

score 1 · Answer 2 · answered Jul 14 '16 at 22:42

There is no 100% reliable way to map rows to transactions, unless you add custom columns.

Using ORA_ROWSCN, even with ROWDEPENDENCIES enabled, is not always accurate. Jack's test is correct, and shows that a single transaction will save all rows with the same SCN. However, it is also possible for a different transaction to create a row with the same SCN.

--Create a table with ROWDEPENDENCIES enabled.
drop table rowscn_test;
create table rowscn_test(a number) rowdependencies;

--Create 100 jobs to insert and commit one row.
begin
    for i in 1 .. 100 loop
        dbms_scheduler.create_job(
            job_name   => 'rowscn_'||i,
            job_type   => 'PLSQL_BLOCK',
            job_action => 'begin insert into rowscn_test values(1); commit; end;',
            enabled    => true
        );
    end loop;
end;
/

--If ORA_ROWSCN was reliable then this query would not return any rows.
select ora_rowscn, count(*)
from rowscn_test
group by ora_rowscn
having count(*) > 1;

Sample results:

ORA_ROWSCN      COUNT(*)
----------      --------
12124183417057   2

The results are not deterministic and will be different for each system. Sometimes the ORA_ROWSCNs are unique and the query returns no results. But I would not rely on that always being true.

Find out if two rows came from same transaction in Oracle

2 Answers2