ORA_ROWSCN continuation…

In my previous post I was wondering how SCN is assigned to ROW or Block. I couldn’t find explanation in Oracle Documentation. But I’ve found this document written by Jonathan Lewis. You should read this! I’m little afraid about 'best guess' technology.
By the way you can also read article about undocumented userenv('commitscn') written in May 1999. When I first saw it I thought it might be replaced by ORA_ROWSCN.
So I’ve try this:

SQL> create table t (n1 number, text varchar2(10)) ROWDEPENDENCIES;

Table created.

SQL> insert into t values (userenv('commitscn'), 'T1');

1 row created.

SQL> select ora_rowscn, n1 commitscn, text from t;

ORA_ROWSCN  COMMITSCN TEXT                                                     
---------- ---------- ----------                                               
             4621851 T1                                                       

SQL> declare
 2    pragma autonomous_transaction;
 3  begin
 4    insert into t values (userenv('commitscn'), 'TAuto');
 5    commit;
 6  end;
 7  /

PL/SQL procedure successfully completed.

SQL> select ora_rowscn, n1 commitscn, text from t;

ORA_ROWSCN  COMMITSCN TEXT                                                     
---------- ---------- ----------                                               
             4621851 T1                                                       
  4621852    4621851 TAuto                                                    

SQL> commit;

Commit complete.

SQL> select ora_rowscn, n1 commitscn, text from t;

ORA_ROWSCN  COMMITSCN TEXT                                                     
---------- ---------- ----------                                               
  4621854    4621853 T1                                                       
  4621852    4621851 TAuto

So for sure ORA_ROWSCN and userenv('commitscn') is not the same. I must say that I’m even more confused.

If You have some spare time, I recommend to read this post: Is it crazy to feel guilty about success? and from Eddie Awad blog: SYS_OP_MAP_NONNULL, REVERSE, LNNVL, NVL, NVL2, COALESCE

Cheers, Paweł

ORA_ROWSCN behavior: bug or feature?

ORA_ROWSCN is pseudo column that gives SCN (System Change Number) for transaction that was modifying record. Lets take a closer look. First create table:

 CREATE TABLE A_TABLE
   (ID NUMBER, TEXT CHAR(2000));

Then feed table with some data:

SQL> declare
  2    procedure auto_commit
  3    is pragma autonomous_transaction;
  4    begin commit; end;
  5  begin
  6    for i in 1..10 loop
  7      insert into a_table select rownum, rownum 
from dual connect by level <=10;
  8      auto_commit;
  9    end loop;
 10  end;
 11  /

Procedure auto_commit is used to force change of SCN. Take a look what we have in table:

SQL> select ora_rowscn, count(*) from a_table
  2  group by ora_rowscn;

ORA_ROWSCN   COUNT(*)                                                           
---------- ----------                                                           
   4589308         24                                                           
   4589313         16                                                           
   4589293         15                                                           
   4589297         24                                                           
   4589302         21                                                           

We ran INSERT statement 10 times and we get 5 different SCN’s in table. None of them has exactly 10 rows. This is because table was created with default setting: NOROWDEPENDENCIES, and SCN is kept on block level. Every and each row in block has the same SCN. Lets commit this transaction:

SQL> commit;

Commit complete.

SQL> select ora_rowscn, count(*) from a_table
  2  group by ora_rowscn;

ORA_ROWSCN   COUNT(*)                                                           
---------- ----------                                                           
   4589363        100                                                           

Now all rows haveexactly the same SCN.
Now lets take a look what will happen for table with ROWDEPENDENCIES on.

 CREATE TABLE A_TABLE_ROW
   (ID NUMBER, TEXT CHAR(2000)) ROWDEPENDENCIES;

Insert some data:

SQL> declare
  2    procedure auto_commit
  3    is pragma autonomous_transaction;
  4    begin commit; end;
  5  begin
  6    for i in 1..10 loop
  7      insert into A_TABLE_ROW select rownum, rownum 
from dual connect by level <=10;
  8      auto_commit;
  9    end loop;
 10  end;
 11  /

Query table:

SQL> select ora_rowscn, count(*) from A_TABLE_ROW
  2  group by ora_rowscn;

ORA_ROWSCN   COUNT(*)                                                           
---------- ----------                                                           
                  100                                                           

Now ORA_ROWSCN is NULL for uncommitted transaction. This is very interesting.

SQL> commit;

Commit complete.

SQL> select ora_rowscn, count(*) from A_TABLE_ROW
  2  group by ora_rowscn;

ORA_ROWSCN   COUNT(*)                                                           
---------- ----------                                                           
   4590773        100                                                           

What will happen when updating:

SQL> update A_TABLE_ROW set id = id where rownum <20;

19 rows updated.

SQL> select ora_rowscn, count(*) from A_TABLE_ROW
  2  group by ora_rowscn;

ORA_ROWSCN   COUNT(*)
---------- ----------
                   19
   4590773         81

We can conclude that data in active transaction have NULL ORA_ROWSCN for tables with ROWDEPENDENCY.
What about Index Organized Tables:

 CREATE TABLE A_TABLE_IOT
   (ID NUMBER primary key, TEXT CHAR(2000)) organization index;

Generate data:

 SQL> declare
  2    procedure auto_commit
  3    is pragma autonomous_transaction;
  4    begin commit; end;
  5  begin
  6    for i in 1..10 loop
  7      insert into A_TABLE_IOT select i*10+rownum, rownum 
from dual connect by level <=10;
  8      auto_commit;
  9    end loop;
 10  end;
 11  /

PL/SQL procedure successfully completed.

SQL> select ora_rowscn, count(*) from A_TABLE_IOT
  2  group by ora_rowscn;

ORA_ROWSCN   COUNT(*)
---------- ----------
                  100

For IOT with NOROWDEPENDENCIES we still get NULLs for ORA_ROWSCN.

Questions:

1. Why we have such differences in implementation of ORA_ROWSCN?
2. We can see that SCN is assigned at commit. Does commit tackles all blocks modified by transaction? For short transaction it is not a problem, as blocks probably are still in buffer. But large transactions that generate lot of redo, and there are few check points during transaction time should not change this blocks. That means that block should have only transaction id, and oracle translates this transaction id to SCN.

Cheers, Paweł