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?
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.
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?
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
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.
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.
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
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.
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,
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
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
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
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
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.
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,
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)
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.
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
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.
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.
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
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 ?
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.