Re: When does oracle allocate a ora_ROWSCN to a transaction

From: Alberto Dell'Era <alberto.dellera_at_gmail.com>
Date: Fri, 9 Mar 2007 22:21:46 +0100
Message-ID: <4ef2fbf50703091321ked1ba00x4f62404b066a2fd@mail.gmail.com>

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_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-l

Received on Fri Mar 09 2007 - 15:21:46 CST