Home » SQL & PL/SQL » SQL & PL/SQL » ORA_ROWSCN functionality
ORA_ROWSCN functionality [message #320774] Fri, 16 May 2008 03:59 Go to next message
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 #320787 is a reply to message #320774] Fri, 16 May 2008 04:37 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Can you copy and paste your sessions in addition to explaining/interpreting what you see.

Regards
Michel
Re: ORA_ROWSCN functionality [message #320800 is a reply to message #320787] Fri, 16 May 2008 04:55 Go to previous messageGo to next message
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


Re: ORA_ROWSCN functionality [message #320824 is a reply to message #320800] Fri, 16 May 2008 06:38 Go to previous message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Why did it have to show BBBBB when you committed CCCCC after?

Regards
Michel

[Updated on: Fri, 16 May 2008 06:39]

Report message to a moderator

Previous Topic: granting privlages to newly created user
Next Topic: IN OUT parameter
Goto Forum:
  


Current Time: Sat Dec 03 13:46:44 CST 2016

Total time taken to generate the page: 0.12051 seconds