Skip to Main Content

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Mahmood.

Asked: October 29, 2007 - 10:08 pm UTC

Last updated: June 08, 2012 - 3:58 pm UTC

Version: 10.2.0

Viewed 10K+ times! This question is

You Asked

Hello Tom,

Lucky enough to get to submit a new question..

On ORA_ROWSCN, when I update a row, it is expected that ORA_ROWSCN number for that particular row is changed. But I noticed this strange behavior...Could you please explain this..

Here is what I had tried:

Thanks in advance for your Help!!

SCOTT@MYDB> select ora_rowscn,empno,ename from emp;

ORA_ROWSCN      EMPNO ENAME
---------- ---------- ----------
   4443685       7369 SMITH
   4443685       7499 ALLEN
   4443685       7521 WARD
   4443685       7566 JONES
   4443685       7654 MARTIN
   4443685       7698 BLAKE
   4443685       7782 CLARK
   4443685       7788 SCOTT
   4443685       7839 KING
   4443685       7844 TURNER
   4443685       7876 ADAMS
   4443685       7900 JAMES
   4443685       7902 FORD
   4443685       7934 MILLER

SCOTT@MYDB> update emp set ename=lower(ename) where empno=7369;

SCOTT@MYDB> commit;

SCOTT@MYDB> select ora_rowscn,empno,ename from emp;

ORA_ROWSCN      EMPNO ENAME
---------- ---------- ----------
   4494942       7369 smith
   4494942       7499 ALLEN
   4494942       7521 WARD
   4494942       7566 JONES
   4494942       7654 MARTIN
   4494942       7698 BLAKE
   4494942       7782 CLARK
   4494942       7788 SCOTT
   4494942       7839 KING
   4494942       7844 TURNER
   4494942       7876 ADAMS
   4494942       7900 JAMES
   4494942       7902 FORD
   4494942       7934 MILLER

Here ORA_ROWSCN number of first row only should have been changed? Isn't it?
Why is it doing for all?

and Tom said...

this is the expected *default* behavior. Here is an excerpt from Expert Oracle Database Architecture that describes what is happening in more detail:

<quote>
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>

Rating

  (31 ratings)

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

Comments

Convinced

Mahmood Lebbai, October 30, 2007 - 3:59 pm UTC

Tom,

I myself experimented that and I got it why now it happens.
Thanks for the quoting from your book..Though I got all three of your books, I did not get to read the section you quoted from.

You are right on! Thanks for you response.

Excellent

chaman, October 31, 2007 - 12:59 pm UTC

Hello Tom,
Thanks for explaining but was wondering in what scenarios the ORA_ROWSCN can be used?
Tom Kyte
November 01, 2007 - 4:22 pm UTC

ora_rowscn number changed by hibernate

A reader, March 18, 2009 - 5:58 pm UTC

Hi Tom,

we are trying to implement ora_rowscn to do optimistic locking and we ran into a problem with Hibernate when we run updates statements, hibernates increments by 1 the ora_rowscn number that we originally got when we first select the rows, triggering the StaleObjectException.
The java developers have tried to resolve this, but they dont' see a way to make using ORA_ROWSCN work with the second-level cache in hibernate. If they could tell Hibernate to invalidate the cache when updates are done instead of putting the updated entity directly into the cache, they think they could make it work. Unfortunately they haven't been able to find a way to modify the cache's behavior.
Have you run into this problem with Hibernate?
Any help you can give us with this problem we will greatly appreciated.

Thanks a lot.
Tom Kyte
March 19, 2009 - 10:15 am UTC

... Have you run into this problem with Hibernate? ...

Nope, but only because I wouldn't be using it....

You'll need to go to the makers of hibernate.

ora_rowscn number changed by hibernate

A reader, March 20, 2009 - 11:18 pm UTC

Thanks for answering.
So, what would you use in a java development environment instead of hibernate?
Tom Kyte
March 24, 2009 - 10:50 am UTC

well, in the olden days, our programmers actually used SQL and took the time to learn the most important component of their application infrastructure - the thing that persisted long after their applications died and went away.

The database.....


What would I use in a <any language, java isn't special> environment?

Stored procedures for transactional API's - not TABLE api's, TRANSACTIONAL apis.

And they would not be coded by the java developers since they haven't read a book on databases yet. They would be developed by people that understand data integrity, locking, concurrency controls, sql, etc...

Really weird ORA_ROWSCN behaviour

Martin Vajsar, April 03, 2009 - 7:49 am UTC

Tom, could you please comment on the really weird results here? I wanted to see what happens to ora_rowscn in session 2 when session 1 has uncommited modifications in the table (rowdependencies on) and I seem to get garbage here. After commiting the changes it is all right again:

SQL> select * from v$version;

BANNER                                                                          
----------------------------------------------------------------                
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bi                
PL/SQL Release 10.2.0.1.0 - Production                                          
CORE 10.2.0.1.0 Production                                                      
TNS for 64-bit Windows: Version 10.2.0.1.0 - Production                         
NLSRTL Version 10.2.0.1.0 - Production                                          

SQL> -- *** SESSION 1 ***
SQL> create table a(id number, text char(2000)) rowdependencies;

Table created.

SQL> insert into a
  2  (select level, 'Level ' || level from dual connect by level <= 100);

100 rows created.

SQL> commit;

Commit complete.

SQL> update a set text = 'Nothing' where id <= 20;

20 rows updated.

SQL> -- *** No commit! ***

Session 2

SQL> -- *** SESSION 2: ***
SQL> select to_Char(ora_rowscn, '999,999,999,999,999,999'), count(*)
  2  from a group by ora_rowscn;

TO_CHAR(ORA_ROWSCN,'999,   COUNT(*)                                             
------------------------ ----------                                             
      35,322,350,018,592         12                                             
              21,408,754         88                                             

SQL> select scn_to_timestamp(ora_rowscn), count(*)
  2  from a group by ora_rowscn;
select scn_to_timestamp(ora_rowscn), count(*)
       *
ERROR at line 1:
ORA-08181: specified number is not a valid system change number 
ORA-06512: at "SYS.SCN_TO_TIMESTAMP", line 1 

Session 1

SQL> -- *** SESSION 1 ***
SQL> commit;

Commit complete.

Session 2

SQL> -- *** SESSION 2: ***
SQL> select to_Char(ora_rowscn, '999,999,999,999,999,999'), count(*)
  2  from a group by ora_rowscn;

TO_CHAR(ORA_ROWSCN,'999,   COUNT(*)                                             
------------------------ ----------                                             
              21,408,796         20                                             
              21,408,754         80                                             

SQL> select scn_to_timestamp(ora_rowscn), count(*)
  2  from a group by ora_rowscn;

SCN_TO_TIMESTAMP(ORA_ROWSCN)                                                    
---------------------------------------------------------------------------     
  COUNT(*)                                                                      
----------                                                                      
03.04.09 13:28:27,000000000                                                     
        20                                                                      
                                                                                
03.04.09 13:28:16,000000000                                                     
        80                                                                      
                                                                                

Tom Kyte
April 03, 2009 - 9:10 am UTC

that is an odd one,

ops$tkyte%ORA10GR2> select to_char( 35322350018592, 'xxxxxxxxxxxxxxxxx' ) from dual;

TO_CHAR(3532235001
------------------
      202020202020




it is as if some of them were blanked out (literally 0x20 is ascii space)

I reproduced in 10gr2 (10.2.0.4)
I cannot reproduce in 10gr1 or 11gr1


ops$tkyte%ORA10GR2> create table a(id number, text char(2000)) rowdependencies;

Table created.

ops$tkyte%ORA10GR2> create or replace procedure p
  2  as
  3          pragma autonomous_transaction;
  4  begin
  5          for x in ( select to_Char(ora_rowscn, '999,999,999,999,999,999') ||
  6                            ', ' || to_char(min(id),'999') || ', ' ||
  7                                            to_char(max(id),'999') data, count(*) cnt
  8                       from a group by to_Char(ora_rowscn, '999,999,999,999,999,999') )
  9          loop
 10                  dbms_output.put_line( '-' || x.data || ' -- ' || x.cnt );
 11          end loop;
 12          commit;
 13  end;
 14  /

Procedure created.

ops$tkyte%ORA10GR2> insert into a (select level, 'Level ' || level from dual connect by level <= 100);

100 rows created.

ops$tkyte%ORA10GR2> commit;

Commit complete.

ops$tkyte%ORA10GR2> exec p
-              82,521,998,    1,  100 -- 100

PL/SQL procedure successfully completed.

ops$tkyte%ORA10GR2> update a set text = 'Nothing' where id <= 20;

20 rows updated.

ops$tkyte%ORA10GR2> exec p
-              82,521,998,    4,  100 -- 88
-      35,322,350,018,592,    1,   15 -- 12

PL/SQL procedure successfully completed.

ops$tkyte%ORA10GR2> commit;

Commit complete.

ops$tkyte%ORA10GR2> exec p
-              82,522,016,    1,   20 -- 20
-              82,521,998,   21,  100 -- 80

PL/SQL procedure successfully completed.




what is strange is that it is not a contiguous set of rows. They are all contained in the first 15 rows (that funky number), but id = 4 for example has the old rowscn.


Looks confusing but I think rather harmless. I don't see any bugs filed for it as yet.

can rman copy of databse

A reader, May 07, 2009 - 5:11 pm UTC

with same dbid and dbname have same ora_rowscn.

Tom Kyte
May 11, 2009 - 1:51 pm UTC

it is called a "full restore", yes.

SCN_TO_TIMESTAMP(ORA_ROWSCN)

Sandeep, August 19, 2009 - 4:08 am UTC

Hi I am trying to get the rowtimestamp of the table while doing it in my machine on my personal database it worked fine.
but when I was trying to do the same in my office with other database it gave me follwing error.

SQL> SELECT ORA_ROWSCN,loc from dept where deptno=10;

ORA_ROWSCN LOC
---------- -------------
1.0385E+13 NEW YORK

SQL> SELECT SCN_TO_TIMESTAMP(ORA_ROWSCN),loc from dept where deptno=10;
SELECT SCN_TO_TIMESTAMP(ORA_ROWSCN),loc from dept where deptno=10
       *
ERROR at line 1:
ORA-08181: specified number is not a valid system change number
ORA-06512: at "SYS.SCN_TO_TIMESTAMP", line 1


Please do tell what exactly is happening here. I tried to find out for ORA-08181 error but didnt find much information.

Tom Kyte
August 24, 2009 - 4:55 pm UTC

could well be that the SCN is just too old (we only keep a mapping for a short period of time)

ops$tkyte%ORA11GR1> select min(ora_rowscn) from sys.obj$;

MIN(ORA_ROWSCN)
---------------
           4126

ops$tkyte%ORA11GR1> select scn_to_timestamp(min(ora_rowscn)) from sys.obj$;
select scn_to_timestamp(min(ora_rowscn)) from sys.obj$
       *
ERROR at line 1:
ORA-08181: specified number is not a valid system change number
ORA-06512: at "SYS.SCN_TO_TIMESTAMP", line 1


Privilege Required

PS, August 25, 2009 - 12:22 pm UTC

Tom, thanks for your explanation on ora_rowscn. But what is the privilege required to query ora_rowscn? User A & B exists on a DB. User A owns table T1 and has given select grant to User B. User B cannot execute the below statement, so just granting select does not cover privilege to select on ora_rowscn?

SELECT ORA_ROWSCN FROM A.T1;

Thanks, PS
Tom Kyte
August 25, 2009 - 8:36 pm UTC

ops$tkyte%ORA10GR2> create user a identified by a default tablespace users quota unlimited on users;

User created.

ops$tkyte%ORA10GR2> create user b identified by b;

User created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> grant create session, create table to a;

Grant succeeded.

ops$tkyte%ORA10GR2> grant create session to b;

Grant succeeded.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> connect a/a
Connected.
a%ORA10GR2> create table t ( x int );

Table created.

a%ORA10GR2> insert into t values (1);

1 row created.

a%ORA10GR2> commit;

Commit complete.

a%ORA10GR2> select x, ora_rowscn from t;

         X ORA_ROWSCN
---------- ----------
         1    4201062

a%ORA10GR2> grant select on t to b;

Grant succeeded.

a%ORA10GR2>
a%ORA10GR2> connect b/b
Connected.
b%ORA10GR2> select x, ora_rowscn from a.t;

         X ORA_ROWSCN
---------- ----------
         1    4201062




show us how to reproduce.

Follow up on privileges required

PS, August 26, 2009 - 6:12 am UTC

Thanks Tom. With the limited access I have this is what I could find out.

User A owns the table T
Grants Select, Delete, Insert, Update on table T to Role R
User B is assigned the role R

Executing the query

SELECT * FROM USER_ROLE_PRIVS; on user B gives the below result

Username Granted_Role Admin_Option Default_Role OS_Granted
B R NO YES NO

SELECT * FROM ROLE_SYS_PRIVS; on user B gives the below result

ROLE PRIVILEGE ADMIN_OPTION
R CREATE SESSION NO

On executing the select query it throws the error below

SELECT ORA_ROWSCN FROM A.T;

An error was encountered performing the requested operation:

ORA-00904: "ORA_ROWSCN": invalid identifier
00904. 00000 - "%s: invalid identifier"
*cause:
*Action:
Error at Line:1 Column:7

With this information, is it possible to find out the casue of the issue? I will try to find out the create user script for user B in parallel.

Thanks, PS.

Tom Kyte
August 26, 2009 - 7:18 pm UTC

take my test case

run it in a test system

alter it to match what YOU do in YOUR system and make it fail.

role or direct grant, it works for me and should work in general.



I want cut and pastes like I give you - I have no idea what you might really be typing.

ops$tkyte%ORA10GR2> drop user a cascade;

User dropped.

ops$tkyte%ORA10GR2> drop user b cascade;

User dropped.

ops$tkyte%ORA10GR2> drop role my_role;

Role dropped.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> create user a identified by a default tablespace users quota unlimited on users;

User created.

ops$tkyte%ORA10GR2> create user b identified by b;

User created.

ops$tkyte%ORA10GR2> create role my_role;

Role created.

ops$tkyte%ORA10GR2> grant create session, create table to a;

Grant succeeded.

ops$tkyte%ORA10GR2> grant create session, my_role to b;

Grant succeeded.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> connect a/a
Connected.
a%ORA10GR2> create table t ( x int );

Table created.

a%ORA10GR2> insert into t values (1);

1 row created.

a%ORA10GR2> commit;

Commit complete.

a%ORA10GR2> select x, ora_rowscn from t;

         X ORA_ROWSCN
---------- ----------
         1    4234024

a%ORA10GR2> grant select on t to my_role;

Grant succeeded.

a%ORA10GR2>
a%ORA10GR2> connect b/b
Connected.
b%ORA10GR2> select x, ora_rowscn from a.t;

         X ORA_ROWSCN
---------- ----------
         1    4234024

update based on row scn

jian huang zheng, January 17, 2010 - 1:41 am UTC

Hi Tom, I am using oracle 10g, and I find that update using row scn results some strange behaviour, like: in the first session:
SQL> select ora_rowscn,a.*  from dept_row_depend a;

ORA_ROWSCN     DEPTNO DNAME          LOC
---------- ---------- -------------- -------------
   1311951         10 ACCOUNTING     New Yorker
   1072031         20 RESEARCH       DALLAS
   1072031         30 SALES          CHICAGO
   1072031         40 OPERATIONS     BOSTON
SQL> update dept_row_depend set deptno=80 where ora_rowscn=1311951;

1 row updated.

in another session , i issued:
SQL> select ora_rowscn,a.*  from dept_row_depend a;

ORA_ROWSCN     DEPTNO DNAME          LOC
---------- ---------- -------------- -------------
   1311951         10 ACCOUNTING     New Yorker
   1072031         20 RESEARCH       DALLAS
   1072031         30 SALES          CHICAGO
   1072031         40 OPERATIONS     BOSTON
SQL>update dept_row_depend set deptno=70 where ora_rowscn=1311951;
It blocked because the first session, and it should be.

But when i committed the first seesion, I would expect that the second session will not find scn 1311951 since the first session changed that row's scn. but oracle still gave an update in the second session once the first session committed, why?

Thanks,

Can you have a look at the above?

jian huang zheng, January 19, 2010 - 1:45 am UTC

Hi Tom,

Can you please take a look at above to clear my doubts?

Thanks,
Tom Kyte
January 19, 2010 - 4:33 pm UTC

currently, you would want to select for update the row by primary key + ora_rowscn to verify and lock the row and then update it.

wierd behaviour of ora_rowscn

A reader, March 23, 2010 - 3:29 am UTC

Hi tom,

I have a wierd problem when using ora_rowscn in my application.

When I give the query below, I get

select d.og_feeder_id, d.transformer_id, d.panel_brd_id, ora_rowscn from t_ss_og_feeder_det d
where panel_brd_id = 11718

og_feeder_id transformer_id panel_brd_id ora_rowscn
23118 23258 11718 13860748
23117 23257 11718 13926853

So, I then try this query using the data above

select d.og_feeder_id, d.transformer_id, d.panel_brd_id, ora_rowscn from t_ss_og_feeder_det d
where og_feeder_id = 23117 AND transformer_id = 23257
AND panel_brd_id = 11718

and this time, it gives me

og_feeder_id transformer_id panel_brd_id ora_rowscn

23117 23257 11718 13860748



why is the ora_rowscn value changing in the second instance? This behaviour is causing problems since I'm using ora_rowscn for optimistic locking and I use its value in the update statement. BTW, the table has been created with ROWDEPENDENCIES setup.

Thank you

Bug with ora_rowscn

A reader, March 24, 2010 - 3:21 am UTC

Hi Tom, 
  I may have figured out what is happening. The difference in the ora_rowscn is evident when we use an index to get to the underlying table data.  Please see below 


SQL> select d.og_feeder_id, d.transformer_id, d.panel_brd_id, ora_rowscn from t_ss_og_feeder_det d 
  2  where panel_brd_id = 10754;

OG_FEEDER_ID TRANSFORMER_ID PANEL_BRD_ID ORA_ROWSCN
------------ -------------- ------------ ----------
       21286          21434        10754     824816
       21285          21433        10754    2709350

SQL> explain plan for 
  2  select d.og_feeder_id, d.transformer_id, d.panel_brd_id, ora_rowscn from t_ss_og_feeder_det d 
  3  where panel_brd_id = 10754;

Explained.

SQL> select * from table (dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 3686466508

----------------------------------------------------------------------------------------
| Id  | Operation         | Name               | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |                    |     2 |    30 |    14   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T_SS_OG_FEEDER_DET |     2 |    30 |    14   (0)| 00:00:01 |
----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------

   1 - filter("PANEL_BRD_ID"=10754)

13 rows selected.


SQL> 
SQL> select d.og_feeder_id, d.transformer_id, d.panel_brd_id, ora_rowscn from t_ss_og_feeder_det d 
  2  where panel_brd_id = 10754
  3  and transformer_id = 21433     ;

OG_FEEDER_ID TRANSFORMER_ID PANEL_BRD_ID ORA_ROWSCN
------------ -------------- ------------ ----------
       21285          21433        10754     824816

SQL> explain plan for 
  2  select d.og_feeder_id, d.transformer_id, d.panel_brd_id, ora_rowscn from t_ss_og_feeder_det d 
  3  where panel_brd_id = 10754
  4  and transformer_id = 21433     ;

Explained.

SQL> select * from table (dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 2128158461

----------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name                           | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                                |     1 |    15 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T_SS_OG_FEEDER_DET             |     1 |    15 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IDX_T_SS_OGFEEDER_TNSFRMRBRDID |     1 |       |     1   (0)| 00:00:01
----------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
---------------------------------------------------

   2 - access("TRANSFORMER_ID"=21433 AND "PANEL_BRD_ID"=10754)

14 rows selected.


********Now the interesting test.  Drop the index and try again.  And now it shows the right values ******************

SQL>   drop index IDX_T_SS_OGFEEDER_TNSFRMRBRDID;

Index dropped.

SQL> select d.og_feeder_id, d.transformer_id, d.panel_brd_id, ora_rowscn from t_ss_og_feeder_det d 
  2  where panel_brd_id = 10754
  3  and transformer_id = 21433     ;

OG_FEEDER_ID TRANSFORMER_ID PANEL_BRD_ID ORA_ROWSCN
------------ -------------- ------------ ----------
       21285          21433        10754    2709350



SQL> explain plan for 
  2  select d.og_feeder_id, d.transformer_id, d.panel_brd_id, ora_rowscn from t_ss_og_feeder_det d 
  3  where panel_brd_id = 10754
  4  and transformer_id = 21433     ;

Explained.

SQL> select * from table (dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 3686466508

----------------------------------------------------------------------------------------
| Id  | Operation         | Name               | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |                    |     1 |    15 |    14   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T_SS_OG_FEEDER_DET |     1 |    15 |    14   (0)| 00:00:01 |
----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------

   1 - filter("TRANSFORMER_ID"=21433 AND "PANEL_BRD_ID"=10754)



**********Now re-create the index and we're back to square one with wrong values in the ora_rowscn fun**********************  
SQL>  CREATE INDEX "DORIS"."IDX_T_SS_OGFEEDER_TNSFRMRBRDID" ON "DORIS"."T_SS_OG_FEEDER_DET" ("TRANSFORMER_ID", "PANEL_BRD_ID") ;

Index created.

SQL> select d.og_feeder_id, d.transformer_id, d.panel_brd_id, ora_rowscn from t_ss_og_feeder_det d 
  2  where panel_brd_id = 10754
  3  and transformer_id = 21433     ;

OG_FEEDER_ID TRANSFORMER_ID PANEL_BRD_ID ORA_ROWSCN
------------ -------------- ------------ ----------
       21285          21433        10754     824816

SQL> 




I think this is definitely a bug!  

    
Thank you, 
Chandini 

A reader, May 18, 2010 - 10:16 am UTC

Hi Tom,

My requirment is to get the last time when a table was either selected/inserted/updated/deleted. I have tried dba_tab_modifications but that didnt yield better results.

I read through this article and tried the following query and when i am executing this query in SQLPLUS

"select scn_to_timestamp(ora_rowscn) from emp;"

I am getting the following error

ERROR at line 1:
ORA-08181: specified number is not a valid system change number
ORA-06512: at "SYS.SCN_TO_TIMESTAMP", line 1

What might be the reason please help me.

Thanks in advance
Tom Kyte
May 24, 2010 - 9:29 am UTC

you need to have auditing enabled then. read about auditing (administrators guide goes into it, security guide as well)


using ora_rowscn or anything like that won't help you at all. You need auditing.

ORA_ROWSCN strange behavior

lengxf, May 25, 2011 - 5:03 am UTC

Hi tom,

I have a wierd problem when using ora_rowscn in my application.

When I give the query below, I get

select d.og_feeder_id, d.transformer_id, d.panel_brd_id, ora_rowscn from t_ss_og_feeder_det d
where panel_brd_id = 11718

og_feeder_id transformer_id panel_brd_id ora_rowscn
23118 23258 11718 13860748
23117 23257 11718 13926853

So, I then try this query using the data above

select d.og_feeder_id, d.transformer_id, d.panel_brd_id, ora_rowscn from t_ss_og_feeder_det d
where og_feeder_id = 23117 AND transformer_id = 23257
AND panel_brd_id = 11718

and this time, it gives me

og_feeder_id transformer_id panel_brd_id ora_rowscn

23117 23257 11718 13860748



why is the ora_rowscn value changing in the second instance? This behaviour is causing problems
since I'm using ora_rowscn for optimistic locking and I use its value in the update statement.
BTW, the table has been created with ROWDEPENDENCIES setup.

Thank you
Tom Kyte
May 25, 2011 - 11:30 am UTC

can you cut and paste that from sqlplus - with

set autotrace on explain


turned on?

ORA_ROWSCN

Shannon Severance, May 25, 2011 - 11:57 am UTC

@lengxf from China: "This behaviour is causing problems since I'm using ora_rowscn for optimistic locking"

Not related to your specific question, but you might want to take a look at a discussion of using ora_rowscn for optimistic locking at: http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:2680538100346782134

ORA_ROWSCN strange behavior

lengxf, May 25, 2011 - 9:16 pm UTC

Hi tom,  
 I have a wierd problem when using ora_rowscn in my application.  
SQL> set autotrace on explain;
SQL> select ROWID,ORA_ROWSCN||'' from ys_dhb12_test_scn where rowid='AADkWXAAjAAFRLvAA4';

ROWID              ORA_ROWSCN||''
------------------ ----------------------------------------
AADkWXAAjAAFRLvAA4 10781735431134


执行计划
----------------------------------------------------------
Plan hash value: 1479049666

--------------------------------------------------------------------------------
----------------

| Id  | Operation                  | Name              | Rows  | Bytes | Cost (%
CPU)| Time     |

--------------------------------------------------------------------------------
----------------

|   0 | SELECT STATEMENT           |                   |     1 |    12 |     1
 (0)| 00:00:01 |

|   1 |  TABLE ACCESS BY USER ROWID| YS_DHB12_TEST_SCN |     1 |    12 |     1
 (0)| 00:00:01 |

--------------------------------------------------------------------------------
----------------


Note
-----
   - 'PLAN_TABLE' is old version

SQL> select ROWID,ORA_ROWSCN||'' from ys_dhb12_test_scn where ora_rowscn= 10781780392034 and to_char(rq,'yyyy-mm-dd')='2004-09-26';

ROWID              ORA_ROWSCN||''
------------------ ----------------------------------------
AADkWXAAjAAFRLvAA4 10781780392034


执行计划
----------------------------------------------------------
Plan hash value: 3495053132

--------------------------------------------------------------------------------
-------

| Id  | Operation         | Name              | Rows  | Bytes | Cost (%CPU)| Tim
e     |

--------------------------------------------------------------------------------
-------

|   0 | SELECT STATEMENT  |                   |    83 |  1660 |  4708   (1)| 00:
00:57 |

|*  1 |  TABLE ACCESS FULL| YS_DHB12_TEST_SCN |    83 |  1660 |  4708   (1)| 00:
00:57 |

--------------------------------------------------------------------------------
-------


Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("ORA_ROWSCN"=10781780392034 AND
              TO_CHAR(INTERNAL_FUNCTION("RQ"),'yyyy-mm-dd')='2004-09-26')

Note
-----
   - 'PLAN_TABLE' is old version
why is the ora_rowscn value changing in the second instance?
Thank you

Tom Kyte
May 26, 2011 - 7:35 am UTC

tell me about ys_dhb12_test_scn, give us the ddl. Also, is there a chance of any migrated rows in that table? can you reproduce this? what version?

ORA_ROWSCN strange behavior

lengxf, May 26, 2011 - 8:55 pm UTC

Hello Tom,

Thanks for you response.

You may need info as below:
一、DDL:
create table YS_DHB12_TEST_SCN
(
DWDM VARCHAR2(21) not null,
DWMC VARCHAR2(200) not null,
ZM VARCHAR2(60) not null,
RQ DATE not null,
CJSJ VARCHAR2(5) not null,
SFJCLLC CHAR(1) not null,
YXSJ NUMBER(5,2) not null,
LYJZM VARCHAR2(60) not null,
JZYL NUMBER(10,3) not null,
JZYYL NUMBER(5,2) not null,
JZYWD NUMBER(5,1) not null,
JZYHS NUMBER(5,2) not null,
JZBSQL NUMBER(6,2),
JZBSQYL NUMBER(5,2),
JZBSQWD NUMBER(5,1),
CBKYL NUMBER(6,2),
JZLL NUMBER(11,3),
BZ VARCHAR2(200),
VRUSERNAME VARCHAR2(20),
LRSJ DATE,
UPLOADFLAG CHAR(1),
EJDWDM VARCHAR2(8),
primary key (DWDM, ZM, RQ, CJSJ, SFJCLLC, LYJZM)
) ROWDEPENDENCIES tablespace YTSJ_APP;

二、reproduce Steps as below:
1、delete data by date
delete from YS_DHB12_TEST_SCN where to_char(rq,'yyyy-mm-dd')>='2010-05-24'
conn.commit();

2、query max ora_rowscn after delete
maxscn=select max(ora_rowscn) maxscn from YS_DHB12_TEST_SCN
maxscn will be used in Step 5.
3、insert new data by date
insert into YS_DHB12_TEST_SCN select * from YS_DHB12@ytsj_cj54 where to_char(rq,'yyyy-mm-dd')>='2010-05-24'
conn.commit();

4、query new-data count by date
select count(*) ADDEDCOUNT from YS_DHB12_TEST_SCN where to_char(rq,'yyyy-mm-dd')>='2010-05-24'
result1:174953

5、query new-data count by scn
select count(*) ADDEDSCNCOUNT from YS_DHB12_TEST_SCN where ora_rowscn>maxscn
result2:221691

result1 is not equals result2,then reproduced it.

三、oracle version is "Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production"

Thanks a lot.
Tom Kyte
May 27, 2011 - 7:17 am UTC

Need your help to recreate if possible.

ops$tkyte%ORA11GR1> create table T
  2  (
  3    DWDM       VARCHAR2(21) not null,
  4    DWMC       VARCHAR2(200) not null,
  5    ZM         VARCHAR2(60) not null,
  6    RQ         DATE not null,
  7    CJSJ       VARCHAR2(5) not null,
  8    SFJCLLC    CHAR(1) not null,
  9    YXSJ       NUMBER(5,2) not null,
 10    LYJZM      VARCHAR2(60) not null,
 11    JZYL       NUMBER(10,3) not null,
 12    JZYYL      NUMBER(5,2) not null,
 13    JZYWD      NUMBER(5,1) not null,
 14    JZYHS      NUMBER(5,2) not null,
 15    JZBSQL     NUMBER(6,2),
 16    JZBSQYL    NUMBER(5,2),
 17    JZBSQWD    NUMBER(5,1),
 18    CBKYL      NUMBER(6,2),
 19    JZLL       NUMBER(11,3),
 20    BZ         VARCHAR2(200),
 21    VRUSERNAME VARCHAR2(20),
 22    LRSJ       DATE,
 23    UPLOADFLAG CHAR(1),
 24    EJDWDM     VARCHAR2(8),
 25    primary key (DWDM, ZM, RQ, CJSJ, SFJCLLC, LYJZM)
 26  ) ROWDEPENDENCIES ;

Table created.

ops$tkyte%ORA11GR1>
ops$tkyte%ORA11GR1> create table tmp as select * from t;

Table created.

ops$tkyte%ORA11GR1>
ops$tkyte%ORA11GR1> exec gen_data( 'TMP', 5000 );

PL/SQL procedure successfully completed.

ops$tkyte%ORA11GR1> update tmp set rq = to_date( '2010-01-01', 'yyyy-mm-dd' )+mod(rownum,365);

5000 rows updated.

ops$tkyte%ORA11GR1> insert into t select * from tmp;

5000 rows created.

ops$tkyte%ORA11GR1> commit;

Commit complete.

ops$tkyte%ORA11GR1>
ops$tkyte%ORA11GR1> delete from T where to_char(rq,'yyyy-mm-dd')>='2010-05-24';

2999 rows deleted.

ops$tkyte%ORA11GR1> commit;

Commit complete.

ops$tkyte%ORA11GR1>
ops$tkyte%ORA11GR1> column maxscn new_val maxscn
ops$tkyte%ORA11GR1> select max(ora_rowscn) maxscn from T;

    MAXSCN
----------
   4084662

ops$tkyte%ORA11GR1>
ops$tkyte%ORA11GR1> insert into T
  2  select DWDM, DWMC, ZM, to_date( '2010-05-25', 'yyyy-mm-dd' ), CJSJ, SFJCLLC,
  3  YXSJ, LYJZM, JZYL, JZYYL, JZYWD, JZYHS, JZBSQL, JZBSQYL, JZBSQWD, CBKYL, JZLL,
  4  BZ, VRUSERNAME, LRSJ, UPLOADFLAG, EJDWDM
  5  from t;

2001 rows created.

ops$tkyte%ORA11GR1> commit;

Commit complete.

ops$tkyte%ORA11GR1>
ops$tkyte%ORA11GR1> select count(*) ADDEDCOUNT from T where
  2  to_char(rq,'yyyy-mm-dd')>='2010-05-24';

ADDEDCOUNT
----------
      2001

ops$tkyte%ORA11GR1>
ops$tkyte%ORA11GR1> select count(*) ADDEDSCNCOUNT from T where ora_rowscn>&maxscn;
old   1: select count(*) ADDEDSCNCOUNT from T where ora_rowscn>&maxscn
new   1: select count(*) ADDEDSCNCOUNT from T where ora_rowscn>   4084662

ADDEDSCNCOUNT
-------------
         2001


that was on 11.1.0.6

gen_data:

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:2151576678914#8743092052176

ORA_ROWSCN strange behavior

lengxf, May 30, 2011 - 10:33 pm UTC


Hello Tom,

Thanks for you response.

I recreated ora_rowscn problem as below:

--version: Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
--create table
drop table YS_DHB12_TEST_SCN12;
create table YS_DHB12_TEST_SCN12
(
id VARCHAR2(21) not null,
RQ DATE not null,
primary key (id)
)rowdependencies;

--create sequence
drop sequence SEQ_LOG_TEST;
create sequence SEQ_LOG_TEST
minvalue 1
maxvalue 999999999999999999999999999
start with 818725
increment by 1
nocache;

--init table YS_DHB12_TEST_SCN12
CREATE OR REPLACE PROCEDURE initdate
IS
BEGIN
FOR i IN 1..800000 LOOP
if i mod 2 = 0 then
insert into YS_DHB12_TEST_SCN12 (id, RQ) values(SEQ_LOG_TEST.NEXTVAL,sysdate);
else
insert into YS_DHB12_TEST_SCN12 (id, RQ) values(SEQ_LOG_TEST.NEXTVAL,sysdate-1);
end if;
END LOOP;
commit;
END initdate;
/

--problem recreate
CREATE OR REPLACE PROCEDURE recreate
as
countAfterDel NUMBER;
realAddedCount NUMBER;
addedCountByScn NUMBER;
maxScnAfterDel varchar(64);
testid varchar(64);
testscn varchar(64);
BEGIN
delete from YS_DHB12_TEST_SCN12 where to_char(rq,'yyyy-mm-dd')=to_char(sysdate,'yyyy-mm-dd');
commit;
select count(*) into countAfterDel from YS_DHB12_TEST_SCN12;
select max(ora_rowscn) into maxScnAfterDel from YS_DHB12_TEST_SCN12;

insert into YS_DHB12_TEST_SCN12 (id, RQ) select SEQ_LOG_TEST.NEXTVAL,sysdate from YS_DHB12_TEST_SCN12;
commit;
select count(*) into realAddedCount from YS_DHB12_TEST_SCN12 where to_char(rq,'yyyy-mm-dd')=to_char(sysdate,'yyyy-mm-dd');
select count(*) into addedCountByScn from YS_DHB12_TEST_SCN12 where ora_rowscn>maxScnAfterDel;
dbms_output.put_line('countAfterDel='||countAfterDel||',maxScnAfterDel='||maxScnAfterDel||',realAddedCount='||realAddedCount||',addedCountByScn='||addedCountByScn);
dbms_output.put_line('select id,ora_rowscn from YS_DHB12_TEST_SCN12 where ora_rowscn>'||maxScnAfterDel||' and to_char(rq,''yyyy-mm-dd'')=''2011-05-30'' and rownum<2;');

select id,ora_rowscn into testid,testscn from YS_DHB12_TEST_SCN12 where ora_rowscn>maxScnAfterDel and to_char(rq,'yyyy-mm-dd')='2011-05-30' and rownum<2;
dbms_output.put_line('result: id='||testid||',ora_rowscn='||testscn);
dbms_output.put_line('select id,ora_rowscn from YS_DHB12_TEST_SCN12 where id='''||testid||''';');
select id,ora_rowscn into testid,testscn from YS_DHB12_TEST_SCN12 where id=testid;
dbms_output.put_line('result: id='||testid||',ora_rowscn='||testscn);
END recreate;
/

exec initdate();
alter table YS_DHB12_TEST_SCN12 add SJZX_GUID varchar2(32) default sys_guid();
set serveroutput on size 30000000;
exec recreate();



Thanks a lot.

ORA_ROWSCN strange behavior

lengxf, May 30, 2011 - 10:46 pm UTC

Hello Tom,

I recreate ora_rowscn problem as below (new):

--version: Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
--create table
drop table YS_DHB12_TEST_SCN12;
create table YS_DHB12_TEST_SCN12
(
id VARCHAR2(21) not null,
RQ DATE not null,
primary key (id)
)rowdependencies;

--create sequence
drop sequence SEQ_LOG_TEST;
create sequence SEQ_LOG_TEST
minvalue 1
maxvalue 999999999999999999999999999
start with 818725
increment by 1
nocache;

--init table YS_DHB12_TEST_SCN12
CREATE OR REPLACE PROCEDURE initdate
IS
BEGIN
FOR i IN 1..800000 LOOP
if i mod 2 = 0 then
insert into YS_DHB12_TEST_SCN12 (id, RQ) values(SEQ_LOG_TEST.NEXTVAL,sysdate);
else
insert into YS_DHB12_TEST_SCN12 (id, RQ) values(SEQ_LOG_TEST.NEXTVAL,sysdate-1);
end if;
END LOOP;
commit;
END initdate;
/

--problem recreate
CREATE OR REPLACE PROCEDURE recreate
as
countAfterDel NUMBER;
realAddedCount NUMBER;
addedCountByScn NUMBER;
maxScnAfterDel varchar(64);
testid varchar(64);
testscn varchar(64);
BEGIN
delete from YS_DHB12_TEST_SCN12 where to_char(rq,'yyyy-mm-dd')=to_char(sysdate,'yyyy-mm-dd');
commit;
select count(*) into countAfterDel from YS_DHB12_TEST_SCN12;
select max(ora_rowscn) into maxScnAfterDel from YS_DHB12_TEST_SCN12;

insert into YS_DHB12_TEST_SCN12 (id, RQ) select SEQ_LOG_TEST.NEXTVAL,sysdate from YS_DHB12_TEST_SCN12;
commit;
select count(*) into realAddedCount from YS_DHB12_TEST_SCN12 where to_char(rq,'yyyy-mm-dd')=to_char(sysdate,'yyyy-mm-dd');
select count(*) into addedCountByScn from YS_DHB12_TEST_SCN12 where ora_rowscn>maxScnAfterDel;
dbms_output.put_line('countAfterDel='||countAfterDel||',maxScnAfterDel='||maxScnAfterDel||',realAddedCount='||realAddedCount||',addedCountByScn='||addedCountByScn);
dbms_output.put_line('select id,ora_rowscn from YS_DHB12_TEST_SCN12 where ora_rowscn>'||maxScnAfterDel||' and to_char(rq,''yyyy-mm-dd'')=to_char(sysdate-1,''yyyy-mm-dd'') and rownum<2;');

select id,ora_rowscn into testid,testscn from YS_DHB12_TEST_SCN12 where ora_rowscn>maxScnAfterDel and to_char(rq,'yyyy-mm-dd')=to_char(sysdate-1,'yyyy-mm-dd') and rownum<2;
dbms_output.put_line('result: id='||testid||',ora_rowscn='||testscn);
dbms_output.put_line('select id,ora_rowscn from YS_DHB12_TEST_SCN12 where id='''||testid||''';');
select id,ora_rowscn into testid,testscn from YS_DHB12_TEST_SCN12 where id=testid;
dbms_output.put_line('result: id='||testid||',ora_rowscn='||testscn);
END recreate;
/

exec initdate();
alter table YS_DHB12_TEST_SCN12 add SJZX_GUID varchar2(32) default sys_guid();
set serveroutput on size 30000000;
exec recreate();



Thanks a lot.

ORA_ROWSCN strange behavior

lengxf, June 01, 2011 - 7:39 pm UTC

Hi Tom,

Can you please take a look at above to clear my doubts?

Thanks,
Tom Kyte
June 02, 2011 - 8:48 am UTC

what version - that DDL changes *everything*

what prompted the ddl?

also, I ran your script:


SP2-0547: size option 30000000 out of range (2000 through 1000000)
countAfterDel=400000,maxScnAfterDel=13808866,realAddedCount=400000,addedCountBy
Scn=400000
select id,ora_rowscn from YS_DHB12_TEST_SCN12 where ora_rowscn>13808866 and
to_char(rq,'yyyy-mm-dd')=to_char(sysdate-1,'yyyy-mm-dd') and rownum<2;
BEGIN recreate(); END;

*
ERROR at line 1:
ORA-01403: no data found
ORA-06512: at "OPS$TKYTE.RECREATE", line 24
ORA-06512: at line 1



what that the expected output?


select ora_rowscn in joins

Srinivas, July 20, 2011 - 12:29 am UTC

im trying to select ora_rowscn from the join of two tables .
But i am getting an error like "ora_rowscn " in valid identifier. I am able to select ora_rowscn in normal cases(not having any joins)

Tom Kyte
July 22, 2011 - 12:43 pm UTC

I don't think you are getting invalid identifier, I think you are getting this (followed by how to use ora_rowscn in a multi-table statement)

ops$tkyte%ORA11GR2> select e.ename, d.dname, ora_rowscn
  2    from scott.emp e, scott.dept d
  3   where e.deptno = d.deptno
  4  /
select e.ename, d.dname, ora_rowscn
                         *
ERROR at line 1:
ORA-00918: column ambiguously defined


ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> select e.ename, d.dname, e.ora_rowscn
  2    from scott.emp e, scott.dept d
  3   where e.deptno = d.deptno
  4  /

ENAME      DNAME          ORA_ROWSCN
---------- -------------- ----------
SMITH      RESEARCH          8190315
ALLEN      SALES             8190315
WARD       SALES             8190315
JONES      RESEARCH          8190315
MARTIN     SALES             8190315
BLAKE      SALES             8190315
CLARK      ACCOUNTING        8190315
SCOTT      RESEARCH          8190315
KING       ACCOUNTING        8190315
TURNER     SALES             8190315
ADAMS      RESEARCH          8190315
JAMES      SALES             8190315
FORD       RESEARCH          8190315
MILLER     ACCOUNTING        8190315

14 rows selected.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> select e.ename, d.dname, e.rscn
  2    from (select deptno, ename, ora_rowscn rscn from scott.emp) e, scott.dept d
  3   where e.deptno = d.deptno
  4  /

ENAME      DNAME                RSCN
---------- -------------- ----------
SMITH      RESEARCH          8190315
ALLEN      SALES             8190315
WARD       SALES             8190315
JONES      RESEARCH          8190315
MARTIN     SALES             8190315
BLAKE      SALES             8190315
CLARK      ACCOUNTING        8190315
SCOTT      RESEARCH          8190315
KING       ACCOUNTING        8190315
TURNER     SALES             8190315
ADAMS      RESEARCH          8190315
JAMES      SALES             8190315
FORD       RESEARCH          8190315
MILLER     ACCOUNTING        8190315

14 rows selected.


ORA_ROWSCN - can we use this to determine the order of commits?

Ratnam, June 02, 2012 - 5:25 am UTC

In our application, we would like to have a column in our transaction table to indicate the order in which the rows get committed into the table.
Can ORA_ROWSCN be used reliably used for this purpose ?

P.S : currently we are using a sequence and insert/update a number column (and serializing the transactions). We want to avoid the serialization and increase the throughput.
Tom Kyte
June 04, 2012 - 7:31 am UTC

not entirely:

http://docs.oracle.com/cd/E11882_01/server.112/e26088/pseudocolumns007.htm#SQLRF50953

<quote>
For each row, ORA_ROWSCN returns the conservative upper bound system change number (SCN) of the most recent change to the row in the current session. This pseudocolumn is useful for determining approximately when a row was last updated. It is not absolutely precise, because Oracle tracks SCNs by transaction committed for the block in which the row resides. You can obtain a more fine-grained approximation of the SCN by creating your tables with row-level dependency tracking. Refer to CREATE TABLE ... NOROWDEPENDENCIES | ROWDEPENDENCIES for more information on row-level dependency tracking.
</quote>

On determining the order of commits

Stew Ashton, June 04, 2012 - 8:20 am UTC


If ORA_ROWSCN is only an approximation, how about using Change Data Capture (CDC)? It can work asynchronously so transaction throughput is less affected.

The change table maintained by CDC would have one row for every DML operation on every line. The SCN and / or commit timestamp from the latest operations could be applied to a companion table, which would then be joined to the original table.

ORA_ROWSCN - can we use this to determine the order of commits?

Ratnam, June 04, 2012 - 3:12 pm UTC

Thanks Tom and Stew for clarifying the details regarding ORA_ROWSCN.

Now if are to use CDC, is there enough information captured in the CDC tables to determine the exact order of commits?
I will need to find a column (or combinations of a columns) to work out the order of commits.

Thanks,
Ratnam
Tom Kyte
June 05, 2012 - 8:05 am UTC

I'm not suggesting this is a good idea, but, you can research this on your own:

http://www.jlcomp.demon.co.uk/commit.html

Change Data Capture (CDC) for commit order

Stew Ashton, June 05, 2012 - 4:39 am UTC


I was more asking a question than proposing a solution. I assume CDC provides accurate information about commit order, since otherwise its usefulness would be quite limited. Please understand I have never used it, I just know it exists. Why not wait for Tom to weigh in?

I forgot to add a link to the documentation. Here it is. Please note that Oracle says it will de-support CDC in a future release! http://docs.oracle.com/cd/E11882_01/server.112/e25554/cdc.htm For the specific answer to your question, see the section "Understanding Change Table Control Columns"

P.S. How well this works might depend on your Oracle version.

From Oracle?

Galen Boyer, June 06, 2012 - 3:22 pm UTC

Hi Tom,

Is there any URL pointing to Oracle's documentation where using
ORA_ROWSCN for optimistic locking is recommended? I'd like to present
to Oracle a case where there is a bug with ora_rowscn and using it in
this way, but I cannot label it a bug if Oracle is not stating it as
an option for optimistic locking.
Tom Kyte
June 06, 2012 - 3:25 pm UTC

there are caveats to using ora_rowscn for optimistic concurrency control. I'm not recommending it myself anymore. I took it out of the books (expert Oracle Database Architecture 2nd Edition)


I've gathered you are not recommending anymore

Galen Boyer, June 07, 2012 - 8:15 am UTC

Hi Tom,

Did Oracle ever recommend it as an optimistic locking feature? I've
always viewed you as a conduit to good practices with Oracle. But,
just because you were recommending this at some point in the past does
not mean Oracle was. There are clearly things you would recommend
that Oracle wouldn't implement, such as "NO WHEN OTHERS". If Oracle
never actually stated that we should be able to replace the tried and
true method of a version_stamp then I won't take time to produce what
is definitely behaviour that does not match the way optimistic locking
should behave.
Tom Kyte
June 07, 2012 - 10:17 am UTC

they do:

http://docs.oracle.com/cd/E11882_01/appdev.112/e25518/adfns_flashback.htm#ADFNS01007

I'll bug that - it doesn't work properly in all cases.


drop table emp;
set echo on

create table emp rowdependencies
as
select * from scott.emp where rownum <= 3;

begin
    for x in (select rowid rid from emp)
    loop
        update emp set ename = ename where rowid = x.rid;
        commit;
    end loop;
end;
/

column empno new_val empno
column ora_rowscn new_val ora_rowscn

select empno, ename, ora_rowscn
  from emp
/

update emp
   set ename = upper(ename)
 where empno = &empno
   and ora_rowscn = &ora_rowscn
/

set echo off
prompt in another session issue:
prompt update emp set ename = 'XXXX' where empno = &empno and ora_rowscn = &ora_rowscn;;
prompt
pause
set echo on
commit;

set echo off
prompt the other session will say 1 row updated, but that is not supposed to have happened



You need to do a select for update, lock the row, then update with the ora_rowscn for it to "work". They are not considering the ora_rowscn to be part of the read consistency bit.


that is, the logic would have to be:

drop table emp;
set echo on

create table emp rowdependencies
as
select * from scott.emp where rownum <= 3;

begin
    for x in (select rowid rid from emp)
    loop
        update emp set ename = ename where rowid = x.rid;
        commit;
    end loop;
end;
/

column empno new_val empno
column ora_rowscn new_val ora_rowscn

select empno, ename, ora_rowscn
  from emp
/

<b>
select *
  from emp
 where empno = &empno
   and ora_rowscn = &ora_rowscn
   FOR UPDATE
/

update emp
   set ename = upper(ename)
 where empno = &empno
   and ora_rowscn = &ora_rowscn
/
</b>

set echo off
prompt in another session issue:
prompt select * from emp where empno = &empno and ora_rowscn = &ora_rowscn FOR UPDATE;;
prompt update emp set ename = 'XXXX' where empno = &empno and ora_rowscn = &ora_rowscn;;
prompt
pause
set echo on
commit;


the select for update will lock and serialize, the update will update zero records if someone else has updated the record.

Because of this nuance - even though we could use it for lost update detection, I'm not recommending it anymore because of the additional complexity.

I don't find the wording

Ravi Vaddepati, June 07, 2012 - 11:31 am UTC

Hi Tom,
In the link, I did not find the wording for optimistic locking. I see just ORA_ROWSCN being described. Could you please explain how this link shows Oracle recommending ORA_ROWSCN for optimistic locking strategies.
Thanks

Tom Kyte
June 07, 2012 - 6:50 pm UTC

optimistic locking is a technique, not an "oracle thing", but a well known database programming technique.

One way to do it is via a counter that is incremented every time a row is modified.

ORA_ROWSCN looked like a neat way to do that - however it has a flaw that prevents it from working without a two step approach.

They don't have to say "optimistic locking", that is inferred from what they are describing. Just like we don't have to say "data modeling" when we say - we decided our schema would be X.



this quote:

<quote>
Your application examines a row of data and records the corresponding ORA_ROWSCN as 202553. Then, your application invokes a package subprogram, whose implementation details you cannot see, which might or might not change the same row (and commit the change). Later, your application must update the row only if the package subprogram did not change it. Make the operation conditional—update the row only if ORA_ROWSCN is still 202553, as in this equivalent interactive statement:
</quote>

describes an optimistic locking scenario.

A) you retrieve a row and remember something about it that will change if someone updates it (that something could be 1) the entire row as you read it out 2) a version/timestamp column that is updated every time the row is modified 3) presumably the ora_rowscn, 4) something else)

B) Something else changes the row - unbeknownst to you. After you read it out, before you attempt to modify it yourself

C) Later you try to update the row - but you shouldn't if that thing in (b) already did - you would overwrite their update without ever taking it into consideration


That is by definition "optimistic concurrency control", you are optimistic that the row won't change between the time you read it and the time you go to update it - but you have something in place you can use at update time to make sure no one changed it between the two events.


So the Summary...

Ratnam, June 08, 2012 - 12:51 pm UTC

ORA_ROWSCN was never intended to represent the real SCN.

ORA_ROWSCN is not dependable for optimistic locking.

ORA_ROWSCN is described as some approximate number that cannot be used for any functionality that requires accuracy levels beyond the approximation adopted by oracle (nobody knows as to what the logic behind this approximation ).

Oracle has a long way to go before it can implement a mechanism to expose the SCN associated with a table row.

Are these correct ?
Tom Kyte
June 08, 2012 - 3:58 pm UTC

I would say "long" is a stretch. It is pretty close.

and when used as described above

select * from t where pk = .... for update;
update set .... where pk = ... AND ora_rowscn = ?


is very dependable. I just chose to not promote it as people might do it wrong - skip the for update.

François Sicard, June 11, 2012 - 8:50 am UTC

Elsewhere you provided the rationale (thanks!) for the necessity of the SELECT FOR UPDATE step, being that ROWSCN changes by themselves don't trigger update restarts. However, we've noticed that a row's SCN can change in an 11g table with ROWDEPENDENCIES on even when the row itself was not directly updated, a behavior which isn't specifically precluded by the description for ROWDEPENDENCIES in the documentation. So although the ROWSCN/SELECT FOR UPDATE method is theoretically sound in that it prevents lost updates, it's flawed in that it provides no guarantee against optimistic locking exceptions for non-overlapping transactions. We see the added complexity you cite as strike one and the spurious exceptions as strike two against using ROWSCN for optimistic locking.

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