Convinced
October 30, 2007 - 3pm Central time zone
Reviewer: Mahmood Lebbai from Bothell, WA
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
October 31, 2007 - 12pm Central time zone
Reviewer: chaman
Hello Tom,
Thanks for explaining but was wondering in what scenarios the ORA_ROWSCN can be used?
ora_rowscn number changed by hibernate
March 18, 2009 - 5pm Central time zone
Reviewer: A reader
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.
Followup March 19, 2009 - 10am Central time zone:
... 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
March 20, 2009 - 11pm Central time zone
Reviewer: A reader
Thanks for answering.
So, what would you use in a java development environment instead of hibernate?
Followup March 24, 2009 - 10am Central time zone:
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
April 3, 2009 - 7am Central time zone
Reviewer: Martin Vajsar from Prague, Czech Republic
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
Followup April 3, 2009 - 9am Central time zone:
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
May 7, 2009 - 5pm Central time zone
Reviewer: A reader
with same dbid and dbname have same ora_rowscn.
Followup May 11, 2009 - 1pm Central time zone:
it is called a "full restore", yes.
SCN_TO_TIMESTAMP(ORA_ROWSCN)
August 19, 2009 - 4am Central time zone
Reviewer: Sandeep from MH India
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.
Followup August 24, 2009 - 4pm Central time zone:
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
August 25, 2009 - 12pm Central time zone
Reviewer: PS from UK
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
Followup August 25, 2009 - 8pm Central time zone:
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
August 26, 2009 - 6am Central time zone
Reviewer: PS from UK
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.
Followup August 26, 2009 - 7pm Central time zone:
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
|