Skip to Main Content

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Matt.

Asked: August 30, 2004 - 8:34 pm UTC

Last updated: May 18, 2012 - 2:23 am UTC

Version: 9.2.0

Viewed 1000+ times

You Asked

I've just having a poke around the doco for 10G. I came across this.

</code> http://download-west.oracle.com/docs/cd/B14117_01/server.101/b10759/pseudocolumns006.htm#sthref707 <code>

Usually in an optimistic locking strategy you would re-query all the columns your where about to update to see if another user has changed the data underneath you (the "lost update problem"). In theory, the approach in the link (ora_scn) might be more efficient since you no longer need to retrieve all these columns - just the system change number (the SCN sequences each change in the DB). Alternatively, you can convert the SCN to a timestamp (to 6 decimal places precision, I think!).

Obviously there must some overhead with retreiving the SCN associated with a changed row. But as the SCN must come from the the block header (I think), then the block must be read by the database in order to get the SCN (and will most likely also pick up the row data that would (hopefully) all be stored ion the same block). So the saving here is the in memory processing of the row in the block. This feels like it should be a small saving, but in a busy high oltp environment any saving would quickly add up.

Q's
===

1) How is the SCN stored in the DB, is there one per changed block, or one per changed row. I'm happy for you to say RTFM, couldn't find anything in the concepts guide though...

2) Has Oracle "measured" the saving here? Are there any figures available?

3) Does my text above make sense ie: Is my understanding correct?

Many Thanks.

Mat.


and we said...

The real savings is that you don't need to SAVE the old values and then COMPARE the old to the new.

For example, say it was a web app (aren't they all). Using HTML. In order to have an address/phone number change screen, you would have to have:

a) the empno
b) the old street address as a hidden field, then the address AGAIN to be typed over
c) the old city as a hidden field, then the city AGAIN to be typed over

and so on for the state, zipcode, area code, phone number etc -- double the data.

Why? so that when the form is submitted back to you -- you have the OLD values, the NEW values -- and you do an update like:

update t
set address = ?, city = ?, state = ?, zip = ?, phone = ?
where empno = ?
AND ( address = ? or (? is null and address is null) )
AND ( city = ? or (? is null and city is null) )
.............


with this SCN thingy, it becomes:

query out empno, scn, other data
save empno, scn as hidden fields
display other data for editing


and the update becomes simply:


update t
set address = ?, city = ?, state = ?, zip = ?, phone = ?
where empno = ?
and scn = ?


Now, many people do this with a "timestamp column" -- eg: they add a column maintained automagically by a trigger and they use this column like the SCN -- every time the row is modified, a new value is placed in there. the timestamp column might be a "timestamp" or a number populated by a sequence.

In 10g, you can use ora_rowscn, but you must be aware of how it works by default, as opposed to how you can make it work.

by default, things are tracked at the block level, so consider:

scott@ORA10G> create table temp as select * from emp;

Table created.

scott@ORA10G>
scott@ORA10G> select ename, ora_rowscn from temp where empno = 7788;

ENAME ORA_ROWSCN
---------- --------------------
SCOTT 8204823211702

scott@ORA10G>
scott@ORA10G> begin
2 for x in ( select empno from temp where empno <> 7788 )
3 loop
4 update temp set ename=ename where empno=x.empno;
5 commit;
6 end loop;
7 end;
8 /

PL/SQL procedure successfully completed.

we updated EVERY THING BUT 7788 on this block

scott@ORA10G>
scott@ORA10G> select ename, ora_rowscn from temp where empno = 7788;

ENAME ORA_ROWSCN
---------- --------------------
SCOTT 8204823211746

but it's row scn changed -- because of the block level tracking. however:

scott@ORA10G>
scott@ORA10G> drop table temp;

Table dropped.

scott@ORA10G> create table temp rowdependencies as select * from emp ;

Table created.

scott@ORA10G>
scott@ORA10G> select ename, ora_rowscn from temp where empno = 7788;

ENAME ORA_ROWSCN
---------- --------------------
SCOTT 8204823211759

scott@ORA10G>
scott@ORA10G> begin
2 for x in ( select empno from temp where empno <> 7788 )
3 loop
4 update temp set ename=ename where empno=x.empno;
5 commit;
6 dbms_lock.sleep(1);
7 end loop;
8 end;
9 /

PL/SQL procedure successfully completed.

scott@ORA10G>
scott@ORA10G> select ename, ora_rowscn from temp where empno = 7788;

ENAME ORA_ROWSCN
---------- --------------------
SCOTT 8204823211759

now it is at the row level -- i added the sleep in there just to show:
scott@ORA10G>
scott@ORA10G> column ts format a33
scott@ORA10G>
scott@ORA10G> select empno, ora_rowscn, scn_to_timestamp(ora_rowscn) ts
2 from temp
3 order by empno
4 /

EMPNO ORA_ROWSCN TS
-------------------- -------------------- ---------------------------------
7369 8204823211767 31-AUG-04 01.47.35.000000000 PM
7499 8204823211770 31-AUG-04 01.47.35.000000000 PM
7521 8204823211773 31-AUG-04 01.47.35.000000000 PM
7566 8204823211776 31-AUG-04 01.47.35.000000000 PM
7654 8204823211779 31-AUG-04 01.47.38.000000000 PM
7698 8204823211782 31-AUG-04 01.47.38.000000000 PM
7782 8204823211785 31-AUG-04 01.47.44.000000000 PM
7788 8204823211759 31-AUG-04 01.47.35.000000000 PM
7839 8204823211788 31-AUG-04 01.47.44.000000000 PM
7844 8204823211871 31-AUG-04 01.47.44.000000000 PM
7876 8204823211874 31-AUG-04 01.47.44.000000000 PM
7900 8204823211877 31-AUG-04 01.47.44.000000000 PM
7902 8204823211880 31-AUG-04 01.47.44.000000000 PM
7934 8204823211883 31-AUG-04 01.47.47.000000000 PM

14 rows selected.

that the conversion of the scn into a timestamp is granular within 3 seconds (eg: not exact! but closer than close enough)


So, that said

1) either or, you decide

2) the size of your web page goes down, the complexity of your queries goes down. to me personally, it is less about "performance" than "ease of development" here.

3) yes.

Rating

  (37 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

amazing feature

Ofir Manor, August 31, 2004 - 3:01 pm UTC

If I had a nickel for every time a saw a project manually implementing that feature!
This is a great feature. Are there any limitation about it's usage, like no support for IOT, partitioning, UTF8, etc?


Tom Kyte
August 31, 2004 - 3:30 pm UTC

well, i've discovered that rowdependencies while supported with IOTs, isn't working properly with ora_rowscn - filed a bug on that.

but there are no documented restrictions, no.

how?

Dave, August 31, 2004 - 7:00 pm UTC

bit OT, but im curious as to how you learn the new features and then learn how to use / implement them.

I know you will have read the docs and seen all the new features, how do you go about practising with them - finding an application for them.

Is there internal oracle training on the 'cool' stuff or simple you self learn everything you can?

Really interested in your methods for applying new technology

Tom Kyte
September 01, 2004 - 7:34 am UTC

self study...

continuing to write code for money (meaning, it is not all esoteric "cool feature", it is "i've got things that need to function day in day out...")

talking with others or just listening in to what they say.... (this is big, huge, really large)

reading questions and thinking of possible solutions, seeing others answers (this is how it all started for me in 1994, on the usenet newsgroups)......



Excellent description.

Matt, August 31, 2004 - 7:23 pm UTC

That was a great explaination.

Some additional questions:

1) Physically how is the block level scn data stored in the block header? In the block header?
2) What about the rows level scn data? again, in the block header?
3) What is the overhead if tracking rowdependencies on a table. I presume there is extra space needed on the block, and some processing overhead. Is this processing overhead measurable?

Many Thanks.

Tom Kyte
September 01, 2004 - 7:44 am UTC

1) it is information in the block header, yes.

2) 6 bytes/row, at the row level.

3) does take extra space, but i've not measured any significant overhead processing wise (cpu or otherwise)

Alessandro Nazzani, November 01, 2004 - 6:34 am UTC

Hi Tom.

I'm experimenting this technique (ORA_ROWSCN) with a simple update web form:

1) load record #1 in my form
2) go to sql-plus, manually update record #1 and commit
3) hit the submit button in the browser and receive the expected error message

So far, so good. But:

1) load record #1 in my form
2) go to sql-plus, manually update record #1
3) hit the submit button in the browser (session hangs)
4) commit the update in sql-plus
5) in the browser I get the "update was successful" message (and record #1 actually contains data changed in the form)

Is this the expected behaviour or am I doing something wrong?

Alessandro

Tom Kyte
November 01, 2004 - 1:24 pm UTC

show us the update code you are using?

To Alessandro

A reader, November 01, 2004 - 1:44 pm UTC

Are you creating the underlying tables with rowdependencies option? You have to do that for using
ora_rowscn for optimistic locking.
Just checking:)

Tom Kyte
November 02, 2004 - 6:32 am UTC

well, only partially true. the ora_rowscn is tracked at the "block level" by default so you would get "false -- sorry row was updated" messages if you didn't enable row dependencies-- which is the opposite of what he is saying above.

I can't understand...

Zhur, November 01, 2004 - 3:19 pm UTC

How Oracle do it?
Let's suppose that we have a long transaction. So long, that after some time Oracle starts write blocks from the buffer to disk.

But we will know SCN only after commit (am I right?), so we must update all blocks again... Great overhead, I think.

Sorry for my english...

Tom Kyte
November 02, 2004 - 6:36 am UTC

no, doesn't work that way. it doesn't go out and update all of the blocks, that would be fairly inefficient.

Alessandro Nazzani, November 02, 2004 - 5:26 am UTC

> show us the update code you are using?

procedure update_notizia (p_old_scn in number,
p_old_rowid in rowid,
p_inevidenza in varchar2,
p_titolo in varchar2,
p_abstract in varchar2,
p_notizia in varchar2,
p_file in varchar2 DEFAULT NULL,
p_id_sezione in number,
P_ABSTRACT_MAXCHARS in number,
P_NOTIZIA_MAXCHARS in number
) as

l_id_upload PM_NEWS_FILES.ID_UPLOAD%TYPE DEFAULT NULL;

BEGIN

If (p_file is not null) Then
select PM_NEWS_FILES.ID_UPLOAD into l_id_upload from PM_NEWS_FILES where PM_NEWS_FILES.NAME=p_file;
End If;

update PM_NEWS_NOTIZIE set inevidenza = p_inevidenza,
titolo = p_titolo,
abstract = p_abstract,
testo = p_notizia,
id_upload = l_id_upload
where rowid = p_old_id_notizia and
ora_rowscn = p_old_scn;

If (SQL%rowcount = 0) Then
htp.bold('Sorry, record was modified, try again.<br>' );
pm_news_forms.p_notizia (p_old_id_notizia);
Else
htp.bold( 'Ok, update successful (ID ' || htf.escape_sc(p_old_id_notizia) || ').<br>');
End If;

END;

And yes, I have (re)created the table with rowdependencies.

Thanks all for your assistance.

Alessandro

Tom Kyte
November 02, 2004 - 8:23 am UTC

I cannot reproduce your issue -- what do you get if you run this test script and have you considered putting out some debug information from your procedure (eg: before you update, select out and print the row you are planning on updating to see what it looks like?)



ops$tkyte@ORA10G> drop table t;
 
Table dropped.
 
ops$tkyte@ORA10G> create table t as select * from scott.emp;
 
Table created.
 
ops$tkyte@ORA10G> column rowid new_val rid
ops$tkyte@ORA10G> column rowscn new_val rscn
ops$tkyte@ORA10G> select rowid, ora_rowscn rowscn, ename, sal from emp where ename = 'KING';
 
ROWID                  ROWSCN ENAME             SAL
------------------ ---------- ---------- ----------
AAAMuKAAEAAAAPMAAI   27321452 KING              605
 
ops$tkyte@ORA10G> declare
  2          pragma autonomous_transaction;
  3  begin
  4          update emp set sal = sal*1.1 where ename = 'KING';
  5          commit;
  6  end;
  7  /
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA10G> update emp set sal = 100 where rowid = '&RID' and ora_rowscn = &RSCN;
old   1: update emp set sal = 100 where rowid = '&RID' and ora_rowscn = &RSCN
new   1: update emp set sal = 100 where rowid = 'AAAMuKAAEAAAAPMAAI' and ora_rowscn =   27321452 
0 rows updated.
 
ops$tkyte@ORA10G> select rowid, ora_rowscn rowscn, ename, sal from emp where ename = 'KING';
 
ROWID                  ROWSCN ENAME             SAL
------------------ ---------- ---------- ----------
AAAMuKAAEAAAAPMAAI   27321472 KING            665.5
 
 

Alessandro Nazzani, November 02, 2004 - 5:35 am UTC

Sorry for the typo: p_old_id_notizia is actually p_old_rowid.

procedure update_notizia (p_old_scn in number,
p_old_rowid in rowid,
p_inevidenza in varchar2,
p_titolo in varchar2,
p_abstract in varchar2,
p_notizia in varchar2,
p_file in varchar2 DEFAULT NULL,
p_id_sezione in number,
P_ABSTRACT_MAXCHARS in number,
P_NOTIZIA_MAXCHARS in number
) as

l_id_upload PM_NEWS_FILES.ID_UPLOAD%TYPE DEFAULT NULL;

BEGIN

If (p_file is not null) Then
select PM_NEWS_FILES.ID_UPLOAD into l_id_upload from PM_NEWS_FILES where PM_NEWS_FILES.NAME=p_file;
End If;

update PM_NEWS_NOTIZIE set inevidenza = p_inevidenza,
titolo = p_titolo,
abstract = p_abstract,
testo = p_notizia,
id_upload = l_id_upload
where rowid = p_old_rowid and
ora_rowscn = p_old_scn;

If (SQL%rowcount = 0) Then
htp.bold('Sorry, record was modified, try again.<br>' );
pm_news_forms.p_notizia (p_old_rowid);
Else
htp.bold( 'Ok, update successful (ID ' || htf.escape_sc(p_old_rowid) || ').<br>');
End If;

END;

Alessandro Nazzani, November 02, 2004 - 9:06 am UTC

I can't reproduce: don't know what I have changed in the last 24 hrs but now it works (browser session does not hang and I get the "already updated" error which, I suppose, is the expected behaviour).

Really sorry for wasting your precious time.

Alessandro

ORA_ROWSCN to add security

Mark Wooldridge, November 02, 2004 - 2:31 pm UTC

This locking strategy used in a web base environment or any type of implementation where the client values being passed to the application could be modified by a malicious client (or an application that contained bugs that generated incorrect ID's) user would provide an additional level of security.

Obviously there should be access control checks such as VPD or procedural code to provide security. This would just add to the security.

The malicious user would need more than just a few ID's to try to attack the application to update information. They would need the ability to get the SCN correct also. This would required read or execute access to the parts of the application that generate the SCN values or guess them correctly. The big key is that when other legitimate users update the values the SCN's would change thus making the value the malicious user needs dynamic.

Given the SCN can be converted to time, could the value be more easily guessed?

Tom Kyte
November 03, 2004 - 6:29 am UTC

but they need to know the ora_rowscn of the row they wanted to update -- NOT the "next scn to be seen in the system"

they need to have queried up the record -- in order to see the rowscn for that record.

so, not sure I agree with your conclusion on this one. seeing what ora_rowscns are being produced won't help me guess the ora rowscn of the 'KING' record.

Added security

Mark Wooldridge, November 05, 2004 - 12:39 pm UTC

The added security would come from the fact that a user may not be able to query up the content but may have guessed the ID's of the record but would have difficulty in guessing the ora_rowscn of the data. In the case where I can query the content but not modify it I would hope the application is smart enough to not bring back the ora_rowscn.

For example the application has a screen to manage names and I know I can see/update names 50, 55, and 60. There is a good chance there are names 49, 51-54, etc. In addtion to the current security of the application not knowing the ora_rowscn of 49 would be that much more difficult to use the application to update name 49.

Not that I would rely on this for security it just adds to the overall security.


Flaw in use of ora_rowscn for optimistic locking?

Andrew Mannering, August 17, 2005 - 7:01 am UTC

My developers have been trying to use ora_rowscn to implement optimistic locking, and have found what appears to be a flaw in it's use.
The ora_rowscn is nulled for a row as soon as the row is modified, and is only repopulated on commit or rollback, so it is impossible to use a RETURNING clause or to re-read the rowscn from within a transaction. Potentially, in a high contention table, the row could be updated by other processes between the time the commit is done and the rowscn is reread by the original process. 

SQL> create table t (tid number, tchar varchar2(10)) 
  2  rowdependencies
  3  /

Table created

SQL> insert into t (tid, tchar) values (1, 'Test')
  2  /

1 row inserted

SQL> commit
  2  /

Commit complete

SQL> select t.tid, t.tchar, ora_rowscn from t
  2  /

       TID TCHAR      ORA_ROWSCN
---------- ---------- ----------
         1 Test       4636918058

SQL> update t set t.tchar = 'Changed' where t.tid = 1
  2  /

1 row updated

[Now in another session update the same row to 'Stomped' - this second session will be blocked by the update from this session]

SQL> select t.tid, t.tchar, ora_rowscn from t
  2  /

       TID TCHAR      ORA_ROWSCN
---------- ---------- ----------
         1 Changed    

[Note the ora_rowscn is null, so we can't save the value yet]

SQL> commit
  2  /

Commit complete

[The second session also commits now...]

SQL> select t.tid, t.tchar, t.ora_rowscn from t
  2  /

       TID TCHAR      ORA_ROWSCN
---------- ---------- ----------
         1 Stomped    4636918190

SQL> 

If the app only rereads the ora_rowscn, it may now have the right SCN, but the wrong data. Rereading the rest of the row has downsides as it could mean reloading a lot more besides - the application is n-tier and so stores a lot of state information in the middle tier.

Given this limititation, they've gone back to using triggers and an incremented number column for optimistic locking.
From what I can glean from the documentation this is not unexpected behaviour, but it does mean it's use for optimistic locking is more limited than first appears (unless this behaviour is a bug!).

Server version is 10.1.0.3 EE on Solaris. 

Tom Kyte
August 17, 2005 - 1:40 pm UTC

If the app only rereads the ora_rowscn

therein lies your mistake, it cannot just re-read the ora_rowscn. the DATA HAS CHANGED, IT WILL BE DIFFERENT on the re-read! period.

How could you NOT re-read it?


You are reading data some other session is in the process of modifying, it will be DIFFERENT after they commit.

I don't see what you could use the data you read out (with the null rowscn) for! Your loser (sorry, meant user) will LOSE when they go to update it by definition!

Followup to Andrew Mannering

Gary, August 17, 2005 - 9:09 pm UTC

"The ora_rowscn is nulled for a row as soon as the row is modified, and is only repopulated on commit or rollback, so it is impossible to use a RETURNING clause or to re-read the rowscn from within a transaction."

Until you commit the update, the row is locked as yours. Whenever you see a NULL ORA_ROWSCN, then that row must contain your uncommitted change (as any other session would show a committed change that has an ORA_ROWSCN).

I think you need to look at your transaction boundaries too.
You should not have an
INSERT ..
COMMIT;
UPDATE ...
COMMIT;

You either need a
INSERT...
UPDATE...
COMMIT;
or
INSERT ...
COMMIT;
SELECT ...
UPDATE ...
COMMIT;

Once you've committed, that row is up for grabs and you have to re-read it before updating it so that you know what you are updating.

Finally, your updates didn't include the check on ORA_ROWSCN. If they had, the update to STOMPED would have failed (zero rows updated), as it would have still had the ORA_ROWSCN from 'Test' and so would not have updated the 'Changed' row.


Some Suprises were in Store for Me with SCN Number

Prasanth Kumar, February 01, 2006 - 6:25 am UTC

Hi Tom,
I have been Following your Site for quite a Long Time. Your Site is I guess Great for any New Database Programmer. Now i am very new to Databases so forgive me if My question is Foolish.

I was actually Looking to Implement Optimistic Locking. And I guess i have finally found the Solution. Now i have found something Funny with SCN.

I had Fired an Update Command in a Oracle Table. only 2 rows were affected but the SCN Number changed for some 6 Rows.

Any Guesses Why???

M i Missing Something...

Tom Kyte
February 02, 2006 - 3:44 am UTC

I presume you are refering to ora_rowscn?  If so, here is an excerpt from my most recent book on that:

<quote src=Expert Oracle: Database Architecture>

Optimistic Locking Using ORA_ROWSCN

Starting with Oracle 10g Release 1, you have the option to use the built-in ORA_ROWSCN function. It works very much like the version column technique described previously, but it can be performed automatically by Oracle—you need no extra column in the table and no extra update/maintenance code to update this value.

ORA_ROWSCN is based on the internal Oracle system clock, the SCN. Every time you commit in Oracle, the SCN advances (other things can advance it as well, but it only advances; it never goes back). The concept is identical to the previous methods in that you retrieve ORA_ROWSCN upon data retrieval, and you verify it has not changed when you go to update. The only reason I give it more than passing mention is that unless you created the table to support the maintenance of ORA_ROWSCN at the row level, it is maintained at the block level. That is, by default many rows on a single block will share the same ORA_ROWSCN value. If you update a row on a block with 50 other rows, then they will all have their ORA_ROWSCN advanced as well. This would almost certainly lead to many false positives, whereby you believe a row was modified that in fact was not. 

Therefore, you need to be aware of this fact and understand how to change the behavior.

To see the behavior and then change it, we’ll use the small DEPT table again:

ops$tkyte@ORA10G> create table dept
  2  (deptno, dname, loc, data,
  3   constraint dept_pk primary key(deptno)
  4  )
  5  as
  6  select deptno, dname, loc, rpad('*',3500,'*')
  7    from scott.dept;
Table created. 

Now we can inspect what block each row is on (it is safe to assume in this case they are in the same file, so a common block number indicates they are on the same block). I was using an 8KB block size with a row width of about 3,550 bytes, so I am expecting there to be two rows per block for this example:

ops$tkyte@ORA10G> select deptno, dname,
  2         dbms_rowid.rowid_block_number(rowid) blockno,
  3             ora_rowscn
  4    from dept;
 
    DEPTNO DNAME             BLOCKNO ORA_ROWSCN
---------- -------------- ---------- ----------
        10 ACCOUNTING          20972   34676029
        20 RESEARCH            20972   34676029
        30 SALES               20973   34676029
        40 OPERATIONS          20973   34676029

And sure enough, that is what we observe in this case. So, let’s update the row where DEPTNO = 10 on block 20972:

ops$tkyte@ORA10G> update dept
  2     set dname = lower(dname)
  3   where deptno = 10;
1 row updated.
 
ops$tkyte@ORA10G> commit;
Commit complete.

What we’ll observe next shows the consequences of ORA_ROWSCN being tracked at the block level. We modified and committed the changes to a single row, but the ORA_ROWSCN values of both of the rows on block 20972 have been advanced:

ops$tkyte@ORA10G> select deptno, dname,
  2         dbms_rowid.rowid_block_number(rowid) blockno,
  3             ora_rowscn
  4    from dept;
 
    DEPTNO DNAME             BLOCKNO ORA_ROWSCN
---------- -------------- ---------- ----------
        10 accounting          20972   34676046
        20 RESEARCH            20972   34676046
        30 SALES               20973   34676029
        40 OPERATIONS          20973   34676029

It would appear to anyone else that had read the DEPTNO=20 row that it had been modified, even though it was not. The rows on block 20973 are “safe”—we didn’t modify them, so they did not advance. However, if we were to update either of them, both would advance. So the question becomes how to modify this default behavior. Well, unfortunately, we have to re-create the segment with ROWDEPENDENCIES enabled.

Row dependency tracking was added to the database with Oracle9i in support of advanced replication, to allow for better parallel propagation of changes. Prior to Oracle 10g, its only use was in a replication environment, but starting in Oracle 10g we can use it to implement an effective optimistic locking technique with ORA_ROWSCN. It will add 6 bytes of overhead to each row (so it is not a space saver compared to the do-it-yourself version column) and that is, in fact, why it requires a table re-create and not just a simple ALTER TABLE: the physical block structure must be changed to accommodate this feature.

Let’s rebuild our table to enable ROWDEPENDENCIES. We could use the online rebuild capabilities in DBMS_REDEFINITION (another supplied package) to do this, but for something so small, we’ll just start over:

ops$tkyte@ORA10G> drop table dept;
Table dropped.
 
ops$tkyte@ORA10G> create table dept
  2  (deptno, dname, loc, data,
  3   constraint dept_pk primary key(deptno)
  4  )
  5  ROWDEPENDENCIES
  6  as
  7  select deptno, dname, loc, rpad('*',3500,'*')
  8    from scott.dept;
Table created.
 
ops$tkyte@ORA10G> select deptno, dname,
  2         dbms_rowid.rowid_block_number(rowid) blockno,
  3             ora_rowscn
  4    from dept;
 
    DEPTNO DNAME             BLOCKNO ORA_ROWSCN
---------- -------------- ---------- ----------
        10 ACCOUNTING          21020   34676364
        20 RESEARCH            21020   34676364
        30 SALES               21021   34676364
        40 OPERATIONS          21021   34676364

We’re back where we were before: four rows on two blocks, all having the same initial ORA_ROWSCN value. Now when we update DEPTNO=10

ops$tkyte@ORA10G> update dept
  2     set dname = lower(dname)
  3   where deptno = 10;
1 row updated.
 
ops$tkyte@ORA10G> commit;
Commit complete.

we should observe the following upon querying the DEPT table:

ops$tkyte@ORA10G> select deptno, dname,
  2         dbms_rowid.rowid_block_number(rowid) blockno,
  3             ora_rowscn
  4    from dept;
 
    DEPTNO DNAME             BLOCKNO ORA_ROWSCN
---------- -------------- ---------- ----------
        10 accounting          21020   34676381
        20 RESEARCH            21020   34676364
        30 SALES               21021   34676364
        40 OPERATIONS          21021   34676364

The only modified ORA_ROWSCN at this point belongs to DEPTNO = 10, exactly what we wanted. We can now rely on ORA_ROWSCN to detect row-level changes for us. 
</quote> 

Followup to My Previous Mail..

Prasanth Kumar, February 01, 2006 - 6:33 am UTC

When i do select distinct(ORA_ROWSCN) from tablename it fetches me only 2 RowSCn Number. So is it possible that one SCN NUmber is mapping to many Rows.???????

Tom Kyte
February 02, 2006 - 3:44 am UTC

see above

ORA_ROWSCN Level of Accuracy

Prasanth Kumar, February 06, 2006 - 2:36 am UTC

Hi Tom,
Thankz a lot for the previous Reply. It was Most Useful. I have just One more small Querry. What is the level of Precission of ORA_ROWSCN. Like i have Heared that TimeStamping dosn't Work Well if 2 transactions are update a row within a timestamp of 16ms. Does this Hold for ORA_ROWSCN also. Will this Number advance for every Transaction.?????

Tom Kyte
February 07, 2006 - 12:31 am UTC

But the flaw with the timestamp 'argument' is - this is for optimistic concurrency control for human interactions. In the time it takes to

a) read out the data
b) format the html page
c) send it to the browser
d) let a human read and interact with it
e) send it back

you are talking seconds, not tenths of seconds, not centiseconds, not milliseconds.

ora_rowscn will advance for each transaction - as would the timestamp in practicality.

ORA_ROWSCN bug with VPD

Massimo Galavotti, February 07, 2006 - 3:12 am UTC

ORA_ROWSCN ??.
Is fine but there is a bug.If a table has a vpd policy and the policy return a where clause oracle trigger an error.
Whe have found this bug.
The bug number is 4947170

Massimo

Tom Kyte
February 07, 2006 - 5:36 am UTC

I see that - I don't like their workaround (doesn't work for any query that returns more than one row).  However, a view can correct this:


ops$tkyte@ORA10GR2> create or replace function my_security_function( p_schema in varchar2,
  2                                        p_object in varchar2 ) return varchar2
  3  as
  4  begin
  5          return '1=1';
  6  end;
  7  /

Function created.

ops$tkyte@ORA10GR2> create table t ( x int );

Table created.

ops$tkyte@ORA10GR2> insert into t values ( 1 );

1 row created.

ops$tkyte@ORA10GR2> create or replace view v_of_t
  2  as
  3  select t.*, ora_rowscn rscn
  4    from t;

View created.

ops$tkyte@ORA10GR2> begin
  2     dbms_rls.add_policy
  3     ( object_schema   => user,
  4       object_name     => 'V_OF_T',
  5       policy_name     => 'MY_POLICY',
  6       function_schema => user,
  7       policy_function => 'My_Security_Function',
  8       statement_types => 'select, insert, update, delete' ,
  9       update_check    => TRUE );
 10  end;
 11  /

PL/SQL procedure successfully completed.

ops$tkyte@ORA10GR2> select * from v_of_t;

         X       RSCN
---------- ----------
         1    2207152
 

ORA_ROWSCN

Prasanth KUmar, February 10, 2006 - 3:04 am UTC

Hi Tom,

In Continuation to my previous Question. My Application is a Distributed Application. We can have many Concurent Users. Picture this Senario which is i Think a flaw in TimeStamping. Also i am using only Optimictic Locking.

User A Updates a Row. at Time X.
User B Reads the row
User C Updates the Row

Now if the update of User A and User C is within the 16ms Time Period- the Timestam dosn't Advance. So User B may well be doing a Dirty Read. So when user B want to update the row he will see that the Timestamp has not changed.

Is My understanding of Timestam Correct. If yes will this also happen if i use ORA_ROWSCN or do you prefer that i use a manually created version col. instead.

Regards

Prasanth

Tom Kyte
February 10, 2006 - 12:47 pm UTC

You are missing the logic. Add into your flow the READS of the row.

When user A read the row, user A would read the timestamp.
When user C read the row, user C would read the timestamp.

User C would not have been able to modify the row.

ORA_ROWSCN

Massimo Galavotti, February 12, 2006 - 8:59 am UTC

Hello Tom first thank for your job.I have tested your example and this works fine for me.But the Oracle manual at the chapter on the "ORA_ROWSCN Pseudocolumn" says "You cannot use this pseudocolumn in a query to a view".
So, you are my SQL hero but

Thank Massimo

Tom Kyte
February 12, 2006 - 11:37 am UTC

right, unless the VIEW exposes the ora_rowscn.

That is why it doesn't work straight out with VPD, VPD uses an inline view to rewrite out query - and then ora_rowscn isn't exposed. By explicitly exposing it - we work around that issue.

Null ORA_ROWSCN indicates uncomitted update?

John G. Galinski, March 29, 2006 - 3:43 pm UTC

I am new to Oracle and come to your web site for insight into how things really work in the world of Oracle. This thread has been fascinating but, what really caught my interest was what I learned when exploring Andrew Mannering's comments on ORA_ROWSCN being NULL. I executed the following and found that it was NULL not only for the session performing the "update" but, also for another session retreiving the data.

session1> create table t (tid number, tchar varchar2(10))
2 rowdependencies;

Table created.

session1> insert into t (tid, tchar) values (1, 'Test');

1 row created.

ilmsdb@ILMSDB.ROBUSTSYSTEMS.COM> commit;

Commit complete.

session1> select t.*,ora_rowscn from t;

TID TCHAR ORA_ROWSCN
---------- ---------- ----------
1 Test 40530552


session1> update t set t.tchar = 'Changed' where t.tid =;

1 row updated.

session1> select t.*,ora_rowscn from t;

TID TCHAR ORA_ROWSCN
---------- ---------- ----------
1 Changed

However from the second session:

session2> select t.*,ora_rowscn from t;

TID TCHAR ORA_ROWSCN
---------- ---------- ----------
1 Test

The second session sees the data as before the update was begun, which is what I expect from Oracle, but is also alerted to the fact that an uncomitted change is in process, by the NULL ORA_ROWSCN.

Im I right in my observations and can this ORA_ROWSCN behavior be used to improve the likely success of the optimistic locking strategy? For example, don't begin a transaction when any of the initial data is found to have NULL ORA_ROWSCNs.

Tom Kyte
March 29, 2006 - 7:11 pm UTC

well, a null is never equal to null so if you get null - you know you will NEVER be able to update the row in the end - the comparison just won't work.

bug 4103979 ?

Alberto Dell'Era, March 30, 2006 - 4:38 am UTC

John's test case doesn't reproduce in 10.2.0.1 :

session1> create table t (tid number, tchar varchar2(10)) rowdependencies;

Table created.

session1> insert into t (tid, tchar) values (1, 'Test');

1 row created.

session1> commit;

Commit complete.

session1> select t.*,ora_rowscn from t;

TID TCHAR ORA_ROWSCN
---------- ------------------------------ ----------
1 Test 153315161

session1> update t set t.tchar = 'Changed' where t.tid =1;

1 row updated.

session1> select t.*,ora_rowscn from t;

TID TCHAR ORA_ROWSCN
---------- ------------------------------ ----------
1 Changed *null*

session2> select t.*,ora_rowscn from t;

TID TCHAR ORA_ROWSCN
---------- ------------------------------ ----------
1 Test 153315161

John's behaviour seems consistent with bug 4103979, found in 10.1.0.3.0 and solved in 10.2.0.0. I was on Solaris 64bit, but it doesn't look like an operating system-dependent bug.

bug 4103979 ?

John G. Galinski, March 31, 2006 - 11:42 am UTC

Thanks Tom and Alberto for the followup. My version is 10.1.0.2.0, which makes my observations moot since the behavior will change when I update.

Problem with ORA_ROWSCN

Jonathan Lewis, June 17, 2006 - 5:09 am UTC

It looks like there's a bug with ORA_ROWSCN that makes it unsafe to use for optimistic locking. It doesn't appear to do the "write consistency" thing if two sessions try to update the same row with the same ora_rowscn, so you can end up with lost updates.

See bug 5270479


Table table re-creation, for ORA_ROWSCN

Tahir, July 05, 2006 - 3:49 am UTC

If I use the "ora_rowscn" in existing tables in production database. Which steps will be involved in populating data, constraints, indexes etc?

Tom Kyte
July 08, 2006 - 7:47 am UTC

all of them?

Not sure what you mean - if you want to use ora_rowscn AND you want it at the row level (not the block level), you would need to rebuild the table with "rowdependencies" (it is not a requirement, ora_rowscn can be useful at the block level for many cases).

You can use dbms_redefinition to do this online (search for that on this site for examples), in 10g - there is a copy_table_dependents routine that automates the copying of constraints and so on - in 9i, you are responsible for doing it.

Pending bug

Matthias, September 07, 2006 - 10:16 am UTC

Hello,

I encountered another pitfall with ora_rowscn.
It's not possible to use the ora_rowscn in a PLSQL insert statement like: "insert into table values rowdata returning ora_rowscn into variable;"

Sad but true.

Bug 5118104 is still pending.




Tom Kyte
September 07, 2006 - 12:00 pm UTC

but why would you need it? what is the use case here?

Pending Bug

Matthias, September 08, 2006 - 5:24 am UTC

Hello,

ok, as I understand the benefit of ora_rowscan is, that you can easily determine if a row has changed last read or not.

So whenever I do a DML on a particular row the ora_rowscn will change. Correct? (Don't bother about row and block issues.)

That means whenever I insert, update or delete a row I will check/read the ora_rowscan to be sure, that noone else has modified the row after my last read.
So the ora_rowscn is passed along with the rowdata to every function in my plsql application.

Its from my point of view exactly that behavior of ora_rowscan I use that the first sentence of your first answer was:"The real savings is that you don't need to SAVE the old values and then COMPARE
the old to the new."

But this means that every simple DML (insert, update, delete) and select fully supports the ora_rowscn in plsql and data integrity.

Inserting a row and reading the same data is not an option, cause I can retrieve the rowid in the returning clause of an insert statement too. (And thats the way I expect the ora_rowscn to work too.)












Tom Kyte
September 09, 2006 - 11:42 am UTC

when you UPDATE or DELETE, you could re-read the ora_rowscn to compare it to the value you read when you first read the row out.

for INSERT - you never read the row out the first time, hence, there is no ora_rowscn to re-read and compare to!


You do not need it for "insert", you just created the row (nothing to re-read) and you have the row locked (no one can modify it till you commit)

Matthias, September 11, 2006 - 4:21 am UTC

Hello,

thank you for your answer.

Lets go into the deep of our application.
In our application we decided to abstract the handling of data from their storage in tables. That means we have some kind of "business objects".
If an user inserts a new object, these object inserts its data into the desired tables - don't knowing when the data will be commited and seen by other users.
This object can also be given to the next method/form which altered the object in some way so that an update will be necessary.
How can this object be sure, that it can make a legal update without having a valid ora_rowscn?

Well, we can discuss if and when it will be necessary that an insert statement should return a new ora_rowscn number or not. As long, as people wanted it (see Bug 5118104), it should be implemented. Before ora_rowscn people added a column to their tables to implement optmistic locking. Thats what they don't need to do anymore if they could use ora_rowscn in the same way the used the extra column.
Why does the behavior from ora_rowscn differs from the old way...?








Tom Kyte
September 11, 2006 - 9:56 am UTC

ah, since you have gone for the seriously "non-performant route", I have no problem just telling you to read it out after the fact. It doesn't really matter since this "abstraction" layer is adding so much overhead anyway.


for enhancement requests, you have to use support. It isn't going to happen because you convince ME, I'm not really relevant in that process.

rowdependencies - online rebuild

Piyush, August 12, 2007 - 3:35 pm UTC

Hi Tom!

As suggested I am trying to rebuild rowdepencies on an existing table.

Please confirm if the following process will work on Oracle 10G (Rel1 and Rel2)...

GRANT execute ON dbms_redefinition TO piyush;


--- logged in with piyush
create table online_original
(
col1 number primary key,
text1 varchar2(10) not null,
text2 varchar2(10)
);

insert into online_original
select rownum, 'sample' || rownum, 'next' || rownum
from user_objects
where rownum <=10;

commit;

-- create table to be used to enable rowdependencies attribute of the table

create table online_staging
(
col1 number primary key,
text1 varchar2(10) not null,
text2 varchar2(10)
) 
rowdependencies;



EXEC dbms_redefinition.can_redef_table ('piyush', 'online_original');

-- process 1 (this is one way)

-- if there is no exception, then proceed
EXECUTE dbms_redefinition.start_redef_table ('piyush', 'online_original', 'online_staging');

EXECUTE dbms_redefinition.SYNC_INTERIM_TABLE ('piyush', 'online_original', 'online_staging');

EXECUTE dbms_redefinition.finish_redef_table ('piyush', 'online_original', 'online_staging');

-- process 2
EXECUTE dbms_redefinition.COPY_TABLE_DEPENDENTS ('piyush',  'online_staging', 'online_original');



Please confirm if I am correct.

Moreover, please let me know if I have missed out on any step which may be essential. (alternative process if any)

Thanks n Regards,
Piyush
Tom Kyte
August 15, 2007 - 10:48 am UTC

well, you have extra steps - but let me ask you this

when you tested it, did it do what you needed.


you have the steps out of order - start redef, copy dependents then finish redef.

Notification of followup via email

Mathew Butler, August 15, 2007 - 1:05 pm UTC

Hi Tom,

I submitted the original question and just received an email advising me that you had added a followup.

I wondered if there was any plans to update the site to allow for changes of email? I've posted questions here under several email addresses and my email has changed ( email changes sometimes, even though my employer hasn't changed in 10 years )

Best Regards,

Clarification of views for UPDATE/DELETE/INSERT coupled with VPD

M Jensen, June 13, 2008 - 6:15 am UTC

Hi,
We would ideally like to use ora_rowscn with rowdependencies enabled in order to implement optimistic locking. However, we also wish to use VPD on some of our tables.
I've managed to expose the ora_rowscn through a view on the base table and to apply the VPD policy to the view. Without the VPD policy on the table I can insert/update/delete through the view - but with the VPD policy I get an ORA-4091 mutating table error.
I may well be wrong but I therefore suspect the VPD implementation uses triggers internally that may cause the mutation (although none are to be found for the table/view in the data dictionary)?
I've therefore toyed with using INSTEAD OF triggers on the view for updates to the table but haven't so far been able to get this to work using ora_rowscn to perform optimistic locking, i.e. that no rows are updated when the SCN has changed.
Would you have any ideas as to how I can workaround this?
Alternatively, I guess a timestamp or revision column would be the way forward in our case.
Thanks in advance for your help and comments.
Tom Kyte
June 13, 2008 - 8:27 am UTC

I need a test case to work with...


ops$tkyte%ORA10GR2> create table dept rowdependencies as select * from scott.dept;

Table created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> create or replace function my_function( p_schema in varchar2, p_object in varchar2 ) return varchar2
  2  as
  3  begin
  4          return 'deptno = 10';
  5  end;
  6  /

Function created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> create or replace view deptv as select dept.*, ora_rowscn scn from dept;

View created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> begin
  2     dbms_rls.add_policy
  3     ( object_schema   => user,
  4       object_name     => 'DEPTV',
  5       policy_name     => 'MY_POLICY',
  6       function_schema => user,
  7       policy_function => 'my_function',
  8       statement_types => 'select, insert, update, delete' ,
  9       update_check    => TRUE );
 10  end;
 11  /

PL/SQL procedure successfully completed.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> select * from dept;

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 Accounting     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON

ops$tkyte%ORA10GR2> select * from deptv;

    DEPTNO DNAME          LOC                  SCN
---------- -------------- ------------- ----------
        10 Accounting     NEW YORK        64971234

ops$tkyte%ORA10GR2> insert into deptv (deptno,dname,loc) values ( 10,'xxx','yyy');

1 row created.

ops$tkyte%ORA10GR2> commit;

Commit complete.

ops$tkyte%ORA10GR2> select * from deptv;

    DEPTNO DNAME          LOC                  SCN
---------- -------------- ------------- ----------
        10 Accounting     NEW YORK        64971234
        10 xxx            yyy             64971260

ops$tkyte%ORA10GR2> update deptv set dname = 'abc' where dname = 'xxx';

1 row updated.

ops$tkyte%ORA10GR2> commit;

Commit complete.

ops$tkyte%ORA10GR2> select * from deptv;

    DEPTNO DNAME          LOC                  SCN
---------- -------------- ------------- ----------
        10 Accounting     NEW YORK        64971234
        10 abc            yyy             64971263

ops$tkyte%ORA10GR2> delete from deptv where dname = 'abc';

1 row deleted.

ops$tkyte%ORA10GR2> commit;

Commit complete.

ops$tkyte%ORA10GR2> select * from deptv;

    DEPTNO DNAME          LOC                  SCN
---------- -------------- ------------- ----------
        10 Accounting     NEW YORK        64971234


Thanks - update_check => TRUE did the trick

M Jensen, June 13, 2008 - 8:58 am UTC

Hi Tom,
Thanks for your response - I was missing update_check => TRUE in order to prevent ORA-4091. I no longer have a need for instead-of triggers.
Thanks for your help, very much appreciated!

Need to specify locking and transaction boundaries in examples

Andrew Wolfe, August 29, 2008 - 12:36 pm UTC

Tom,

When I read this in conjunction with the disclaimers in the Oracle docs, I am concluding that a SELECT FOR UPDATE NOWAIT is needed to prevent race conditions between separate sessions accessing the same row.

Also, I think you need to more fully describe the COMMITs interior to your plsql loops in order to make clear what are the transaction boundaries.

Your answer makes sense in the three-tier world with brief database interactions, and pooled connections that are cleaned between uses.

For those still in a client/server mindset with 'static' DB sessions holding transactions open for long periods, I feel you need to clarify how to use ORA_ROWSCN for optimistic locking.

Best,

Andrew Wolfe
Tom Kyte
August 30, 2008 - 9:51 am UTC

... SELECT FOR UPDATE NOWAIT ...

that is typically used to prevent a LOST UPDATE, yes - a classic database issue that goes way way back in time.

search this site for:

http://asktom.oracle.com/pls/ask/search?p_string=%22pessimistic+locking%22+%22optimistic+locking%22

for more in depth discussions on select for update, versus something like ora_rowscn for lost update detection and prevention.

Optimistic Locking Approach Doesn't Work

Jumping Jimmy Jeeves, September 12, 2008 - 9:31 am UTC

The approach described for optimistic locking doesn't work:

update <some_table>
   set <some_columns> = <some_values>
    where <predicated key column> = <some key value>
      and  ora_rowscn = p_old_scn;

If (SQL%rowcount = 0) Then
   <raise an exception>
End If;


In the case where two (or more) sessions concurrently update the same record. Subsequent updates enqueues behind the first.

When the first session commits, the second session proceeds with the update even though the ora_rowscn has changed because it consistently sees the unchanged value of ora_rowscn.

This isn't a flaw of ora_rowscn, just a failure of the approach.
Tom Kyte
September 16, 2008 - 1:37 pm UTC

arg, in progress bug 7338384


suggested workaround

select for update by ora_rowscn
then update by ora_rowscn

thanks for that followup, appreciate it.

Henry, September 12, 2008 - 2:57 pm UTC

It appears that the bug referenced by Jonathan Lewis arises because ora_rowscn is not dealt with properly in the purview of write consistency.

According to the 11g Oracle Advanced Application Developer¿s Guide, ora_rowscn the ¿Uses of ORA_ROWSCN in application development include concurrency control and client cache invalidation.¿ However, it cannot be used in an optimistic locking scenario since ora_rowscn is not handled properly within the scope of Write Consistency (http://tkyte.blogspot.com/2005/08/something-different-part-i-of-iii.html). Write Consistency makes sure that updates are applied to a consistent set of data.

An update statement is effectively comprised of a SELECT which gathers the rows to be updated, followed by the update itself. Oracle¿s Write Consistency checks that the values which are used to collect the result set to be updated have not changed. We can easily see different behavior, depending on the SQL filters (WHERE clause) as demonstrated in the following cases. We also filter on ora_rowscn to see how it (mis)behaves. 

Our tests used a table, FOO, containing three rows of data.

DROP TABLE foo purge;

CREATE TABLE foo (x number, y number) ROWDEPENDENCIES;

INSERT INTO foo VALUES (1,1);

commit;

INSERT INTO foo VALUES (2,2);

commit;

INSERT INTO foo VALUES (3,3);

commit;

Our two cases consist of:
1. Session #1: update x for all rows in FOO.
2. Session #2: update x for a single row in FOO. This row is filtered either by x (which will change after session #1 commits) or by y. This update waits until session #1 commits.
3. Session #1: commit
4. final result set depends on whether step 2 filtered on x or y. 

CASE #1
In Case #1, session #2 selects the rows to update based on the value of y, which does not change. So the record selected at the outset is updated using the post-commit (current) value of x. 

sess1 21:16:02> update foo set x=x+1 where y>0;

3 rows updated.

sess2 21:17:55> update foo set x=2*x where y=2;

sess1 21:18:22> commit;

Commit complete.

[sess2] 1 row updated.

sess2 21:19:18> select * from foo;

         X          Y
---------- ----------
         2          1
         6          2
         4          3


CASE #2
In Case #2, session #2 selects the rows based on the value of x, which changes. So it needs to reselect the result set after session #1 commits. It does this prior to its update. The update is thus applied to a different row.

sess1 21:12:54> update foo set x=x+1 where y>0;

3 rows updated.

sess2 21:12:44> update foo set x=2*x where x=2;

sess1 21:13:19> commit;

Commit complete.

[sess2] 1 row updated.

sess2 21:15:02> select * from foo;

         X          Y
---------- ----------
         4          1
         3          2
         4          3

ORA_ROWSCN
What if we filter session #2 on ora_rowscn? Since the commit from session #1 changes the value of ora_rowscn, one would expect this case would be analogous to Case #2. IT IS NOT!! It behaves, incorrectly It follows case #1. 

sess1 21:24:37> update foo set x=x+1 where y>0;

3 rows updated.

sess2 21:20:35> update foo set x=2*x where ora_rowscn=11138156;

sess1 21:25:14> commit;

Commit complete.

[sess2] 1 row updated.

sess2 21:26:39> select * from foo;

         X          Y
---------- ----------
         2          1
         6          2
         4          3


We can further confirm that this is a Write Consistency issue by examining the state of the blocks in buffer cache (using x$bh). To do this, we flush the buffer cache following session #2¿s update statement (step #2) and query x$bh (v$bh would have been sufficient for this, but I was also looking at some extra data) after session #1¿s commit (step #3).
An UPDATE requires both consistent reads (cr) and current reads (xcur) of data blocks. The cr gets a consistent result set of rows to be updated. It effectively SELECTs the rows using the WHERE clause in the UPDATE statement. After collecting these rows, a current read is done prior to update in order to make sure we still have a consistent set. If the filtered field has changed in any of these records we no longer have a consistent set of records and need to redo the SELECT using another cr. 

CASE #1
Flushing and querying the buffer cache immediately after session #1 commits, we find a single xcur buffer.
monitor 21:19:11> /

ADDR                  FILE#     BLOCK# STATUS
---------------- ---------- ---------- ----------
0000002A96DAEFF8          4        997 free
0000002A96DAF140          4        997 free
0000002A96DAF288          4        997 free
0000002A96DAF3D0          4        997 free
0000002A96DAF518          4        997 free
0000002A96DAF660          4        997 free
0000002A96DAF7A8          4        997 xcur

7 rows selected.

CASE #2
monitor 21:14:52> @xbh

ADDR                  FILE#     BLOCK# STATUS
---------------- ---------- ---------- ----------
0000002A96DAF660          4        997 cr
0000002A96DAF7A8          4        997 xcur

As expected, we now have an extra cr, as we need to redo the select part of our statement.

ORA_ROWSCN
monitor 21:26:32> @xbh

ADDR                  FILE#     BLOCK# STATUS
---------------- ---------- ---------- ----------
0000002A96A0E9A0          4        997 free
0000002A96A0EAE8          4        997 free
0000002A96A0EC30          4        997 free
0000002A96A0ED78          4        997 free
0000002A96DAEFF8          4        997 free
0000002A96DAF140          4        997 free
0000002A96DAF288          4        997 free
0000002A96DAF3D0          4        997 free
0000002A96DAF518          4        997 free
0000002A96DAF660          4        997 free
0000002A96DAF7A8          4        997 xcur

11 rows selected.


We see that the additional consistent read never took place, even though it should have. 

Apparently, ora_rowscn is not being treated as real data even though it is stored with the rest of the row¿s data in the data block when the table is created using ROWDEPENDENCIES. Just because ora_rowscn is called a pseudo-column doesn¿t mean that its data is not physically stored. 





enabling rowdependencies in existing table

Piyush, September 21, 2011 - 6:10 am UTC

Hi Tom, After 3 years when I again faced the issue. I tried again to use dbms_redefinition package to enable rowdepencies in Oracle 10G Rel 2 table. Unfortunately, when i extracted back the definition of the original table, it did not show up rowdependencies element. Could you please confirm.
Tom Kyte
September 21, 2011 - 9:41 am UTC

ops$tkyte%ORA10GR2> create table t ( x int );

Table created.

ops$tkyte%ORA10GR2> select dbms_metadata.get_ddl( 'TABLE', 'T' ) from dual;

DBMS_METADATA.GET_DDL('TABLE','T')
-------------------------------------------------------------------------------

  CREATE TABLE "OPS$TKYTE"."T"
   (    "X" NUMBER(*,0)
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "USERS"



ops$tkyte%ORA10GR2> alter table t enable row movement;

Table altered.

ops$tkyte%ORA10GR2> select dbms_metadata.get_ddl( 'TABLE', 'T' ) from dual;

DBMS_METADATA.GET_DDL('TABLE','T')
-------------------------------------------------------------------------------

  CREATE TABLE "OPS$TKYTE"."T"
   (    "X" NUMBER(*,0)
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "USERS"  ENABLE ROW MOVEMENT




no problem for me in 10.2.0.5

Rowdepencies

Piyush Agarwal, October 13, 2011 - 2:34 am UTC

Hi Tom, I asked about enabling row dependencies [ora_rowscn feature] but you replied about enabling row movement which is quite simple.
Thanks n Regards,
Piyush Agarwal
Tom Kyte
October 13, 2011 - 7:39 am UTC

you are correct sorry about that.

ops$tkyte%ORA10GR2> create table t1
  2  as
  3  select * from all_objects;

Table created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> alter table t1 add constraint t1_pk primary key(object_id);

Table altered.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> create table t2 (
  2  OWNER                  VARCHAR2(30),
  3  OBJECT_NAME            VARCHAR2(30),
  4  SUBOBJECT_NAME         VARCHAR2(30),
  5  OBJECT_ID              NUMBER,
  6  DATA_OBJECT_ID         NUMBER,
  7  OBJECT_TYPE            VARCHAR2(19),
  8  CREATED                DATE,
  9  LAST_DDL_TIME          DATE,
 10  TIMESTAMP              VARCHAR2(19),
 11  STATUS                 VARCHAR2(7),
 12  TEMPORARY              VARCHAR2(1),
 13  GENERATED              VARCHAR2(1),
 14  SECONDARY              VARCHAR2(1),
 15  NAMESPACE              NUMBER,
 16  EDITION_NAME           VARCHAR2(30)
 17  ) rowdependencies;

Table created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> exec dbms_redefinition.can_redef_table( user, 'T1' );

PL/SQL procedure successfully completed.

ops$tkyte%ORA10GR2> exec dbms_redefinition.start_redef_table( user, 'T1', 'T2', orderby_cols => 'OBJECT_ID' )

PL/SQL procedure successfully completed.

ops$tkyte%ORA10GR2> variable nerrors number
ops$tkyte%ORA10GR2> begin
  2          dbms_redefinition.copy_table_dependents
  3          ( user, 'T1', 'T2',
  4            copy_indexes => dbms_redefinition.cons_orig_params,
  5            num_errors => :nerrors );
  6  end;
  7  /

PL/SQL procedure successfully completed.

ops$tkyte%ORA10GR2> print nerrors

   NERRORS
----------
         0

ops$tkyte%ORA10GR2> exec dbms_redefinition.finish_redef_table( user, 'T1', 'T2' );

PL/SQL procedure successfully completed.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> drop table t2;

Table dropped.

ops$tkyte%ORA10GR2> select dbms_metadata.get_ddl( 'TABLE', 'T1' ) from dual;

DBMS_METADATA.GET_DDL('TABLE','T1')
-------------------------------------------------------------------------------

  CREATE TABLE "OPS$TKYTE"."T1"
   (    "OWNER" VARCHAR2(30) CONSTRAINT "SYS_C0012216" NOT NULL ENABLE NOVALIDATE,

        "OBJECT_NAME" VARCHAR2(30) CONSTRAINT "SYS_C0012217" NOT NULL ENABLE NOVALIDAT
E,
        "SUBOBJECT_NAME" VARCHAR2(30),
        "OBJECT_ID" NUMBER CONSTRAINT "SYS_C0012218" NOT NULL ENABLE NOVALIDATE,
        "DATA_OBJECT_ID" NUMBER,
        "OBJECT_TYPE" VARCHAR2(19),
        "CREATED" DATE CONSTRAINT "SYS_C0012219" NOT NULL ENABLE NOVALIDATE,
        "LAST_DDL_TIME" DATE CONSTRAINT "SYS_C0012220" NOT NULL ENABLE NOVALIDATE,
        "TIMESTAMP" VARCHAR2(19),
        "STATUS" VARCHAR2(7),
        "TEMPORARY" VARCHAR2(1),
        "GENERATED" VARCHAR2(1),
        "SECONDARY" VARCHAR2(1),
        "NAMESPACE" NUMBER,
        "EDITION_NAME" VARCHAR2(30),
         CONSTRAINT "T1_PK" PRIMARY KEY ("OBJECT_ID")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "USERS"  ENABLE NOVALIDATE
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "USERS" <b>ROWDEPENDENCIES
</b>


ops$tkyte%ORA10GR2> drop table t1;

Table dropped.



you'll have to help me reproduce your issue. I get rowdependencies just fine.

timestamp or number version column

Branka, May 17, 2012 - 9:48 am UTC

If I use timestamp or number version column, and 2 transactions try to update same record at the same time (millisecond precision)
Update aa
Where pk_aa = :pk_aa1
And version_id=:old_version_id
Will second transaction overwrite first one? How can I test it? How would it be different than using ORA_ROWSCN?

Tom Kyte
May 18, 2012 - 2:23 am UTC

in order for the millisecond problem to exist the following would have to be true:

a) the record was successfully updated and committed by some transaction at time X (the update and commit happen in the same millisecond)
b) the record was read out by two NEW transactions at exactly time X (still the same millisecond)
c) the record was updated by the two new transactions (and at least one of them committed!) at exactly time X (the same millisecond still)


All of the above has to happen to that record in a single millisecond.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library