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

Answer 1

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