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. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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
up vote 8 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
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

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.

share|improve this answer

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.