Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question
add comment

1 Answer

up vote 6 down vote accepted

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                        
    
share|improve this answer
    
Just a clarify, a different ORA_ROWSCN on two rows tells me that the two rows where not last changed in the same transaction? –  Franz Kafka Nov 26 '11 at 13:04
    
exactly - "Oracle Database assigns every committed transaction a unique SCN.", and with rowdependencies, the SCN of the last transactions on that row is stored with the row (and queried with ora_rowscn) –  Jack Douglas Nov 26 '11 at 13:24
    
and welcome to dba.se :) –  Jack Douglas Nov 26 '11 at 13:27
1  
Jack Douglas is entirely correct, but just so that it is clear, without row dependencies (the default) rows with the same SCN are not necessarily part of the same transaction. They may be from separate transactions that are in the same block. This can be seen in his demonstration by the fact that 10000 update transactions produces only 16 different ora_rowscn values. Many of the rows from separate transactions share the same ora_rowscn because they are in the same block. –  Leigh Riffel Nov 28 '11 at 15:15
    
thanks @Leigh - I realise now I didn't make that too clear in my answer. –  Jack Douglas Nov 28 '11 at 16:01
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.