ORA_ROWSCN functionality [message #320774] |
Fri, 16 May 2008 03:59  |
ora_2007
Messages: 430 Registered: July 2007 Location: Mumbai
|
Senior Member |
|
|
Hi,
I would like to know the thing about ORA_ROWSCN which in Oracle 10g.
1. Please do as follows
1. create table
CREATE TABLE T1
( "C1" NUMBER,
"C2" VARCHAR2(10),
CONSTRAINT "CP1" PRIMARY KEY ("C1")
)
2. insert some values
insert into t1 values(1,'AAAAAA');
3. create below procedure
create or replace procedure Update_t1(i_c1 number,
i_c2 varchar2,
i_ORASCN number) is
begin
update t1
set c2 = i_c2
where c1 = i_c1
and ORA_ROWSCN = i_ORASCN;
end;
4. now execute following query
select c1, c2, ORA_ROWSCN--, scn_to_timestamp(ORA_ROWSCN)
from t1 order by c1;
5. Note ORA_ROWSCN selected say it as ORA_ROWSCN_current_values
(this will be always new when records gets updated, but it will be changed only after commit)
6. Now excute above procedure in session1 as follows (do not commit after this step)
begin
-- Call the procedure
update_t1(i_c1 => 1,
i_c2 => 'BBBBB',
i_orascn => Paste noted value of ORA_ROWSCN i.e ORA_ROWSCN_current_values);
end;
7. Now excute same procedure in session2 as follows (do not commit after this step)
begin
-- Call the procedure
update_t1(i_c1 => 1,
i_c2 => 'CCCCC',
i_orascn => Paste noted value of ORA_ROWSCN i.e ORA_ROWSCN_current_values);
end;
Now this session will be busy until session1 do commit as both modifying same row
8. Now do commit on session1 i.e. for 'BBBBB'
9. Now do commit on session2 i.e. for 'CCCCC'
10. Now check value for c2 in table
select c1, c2, ORA_ROWSCN--, scn_to_timestamp(ORA_ROWSCN)
from t1 order by c1;
Actually it should show 'BBBBB' according to functionality of ORA_ROWSCN. as after first session get commited value of ORA_ROWSCN changes automatically so 2nd session update statement where condition fails and session2 should not modify any row
But on my system its showing 'CCCCC' i.e second session still working.
Do i think in a correct way?
Please let me know about it.
Thanks in advance.
|
|
|
|
Re: ORA_ROWSCN functionality [message #320800 is a reply to message #320787] |
Fri, 16 May 2008 04:55   |
ora_2007
Messages: 430 Registered: July 2007 Location: Mumbai
|
Senior Member |
|
|
SQL> SELECT c1, c2, ORA_ROWSCN--, scn_to_timestamp(ORA_ROWSCN)
FROM t1 ORDER BY c1; 2
C1 C2 ORA_ROWSCN
---------- ---------- ----------
1 AAAAA 1586175040
1st session
SQL> BEGIN
update_t1(i_c1 => 1, i_c2 => 'BBBBB', i_orascn => 1586175040);
END;
/
PL/SQL procedure successfully completed.
2nd session
SQL> BEGIN
update_t1(i_c1 => 1, i_c2 => 'CCCCC', i_orascn => 1586175040);
END;
/
PL/SQL procedure successfully completed.
It gets locked now.
1st session
SQL> commit;
Commit complete.
2nd session.
Lock gets released
SQL> commit;
Commit complete.
SQL> SELECT c1, c2, ORA_ROWSCN FROM t1 ORDER BY c1;
C1 C2 ORA_ROWSCN
---------- ---------- ----------
1 CCCCC 1586175197
|
|
|
|