Search Oracle Blogs

List of Blogs maintained by Paweł Barut.


Thursday, September 21, 2006

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ł

1 comment:

Unknown said...

Your post was great ... this undocumented feature was exactly what I wanted
Thank you
khair

 

Copyright © Paweł Barut
Printing from DOS to USB Printer