Re: When does oracle allocate a ora_ROWSCN to a transaction

At the end.

Simple to see (10.2.0.3):

SQL> create table t (x int) ROWDEPENDENCIES;

Table created.

SQL> column scn form 99999999999999999999999
SQL> select dbms_flashback.get_system_change_number scn from dual;

                    SCN
------------------------
          6969135063616

SQL> insert into t(x) values (1);

1 row created.

SQL> select ora_rowscn from t;

ORA_ROWSCN
----------


SQL> exec dbms_lock.sleep (10);

PL/SQL procedure successfully completed.

SQL> select dbms_flashback.get_system_change_number scn from dual;

                    SCN
------------------------
          6969135063802

SQL> commit;

Commit complete.

SQL> select dbms_flashback.get_system_change_number scn from dual;

                    SCN
------------------------
          6969135063804

SQL> column ora_rowscn form 99999999999999999999999
SQL> select ora_rowscn from t;

             ORA_ROWSCN
------------------------
          6969135063803

So - ora_rowscn is undefined (null) before the transaction ends, and
is assigned at commit time, note that in my case:
6969135063802 < ora_rowscn < 6969135063803

It's not always so precise, the Sql Reference says, about the
"NOROWDEPENDENCIES | ROWDEPENDENCIES" clause:

"This clause lets you specify whether table will use row-level
dependency tracking.
With this feature, each row in the table has a system change number (SCN) that
represents a time greater than or equal to the commit time of the last
transaction that modified the row."

Note "a time greater than or equal".

HTH
Al

On 3/9/07, Adi Alurkar <aalurkar@xxxxxxxxxxxx> wrote:

Greetings,

At what point does Oracle allocate a ora_ROWSCN to a transaction? Is the
ora_ROWSCN allocated when the transaction commences or when it is committed?
i.e.
transaction T1 starts at 00:00:00 on 01/01/2007 and runs for 10 minutes
(ends at 00:09:59 01/01/2007)
transaction T2 starts at 00:02:00 on 01/01/2007 and runs for 4 minutes (ends
at 00:06:59 01/01/2007)
transaction T3 starts at 00:04:00 on 01/01/2007 and runs for 3 minutes (end
at 00:07:59 01/01/2007)


which of the following are true ?

T1.ora_ROWSCN > T3.ora_ROWSCN and T3.ora_ROWSCN

or

T3.ora_ROWSCN > T2.ora_ROWSCN > T1.ora_ROWSCN

---
Adi Alurkar
aalurkar@xxxxxxxxxxxx








--
Alberto Dell'Era
"Per aspera ad astra"
--
http://www.freelists.org/webpage/oracle-l

Other related posts: