Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Mailing Lists -> Oracle-L -> 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 99999999999999999999999SQL> 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 99999999999999999999999SQL> 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_at_linkedin.com> 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_at_linkedin.com
>
>
>
>
>
-- Alberto Dell'Era "Per aspera ad astra" -- http://www.freelists.org/webpage/oracle-lReceived on Fri Mar 09 2007 - 15:21:46 CST
![]() |
![]() |