Re: Oracle Bug?

From: Andy Hassall <andy_at_andyh.co.uk>
Date: Fri, 22 Apr 2005 23:22:44 +0100
Message-ID: <67ui61hsu69iodboa6pcna5hcpo53uo720@4ax.com>

On Fri, 22 Apr 2005 21:45:36 +0100, Andy Hassall <andy_at_andyh.co.uk> wrote:

> Are you perhaps thinking that DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER should
>return the SCN as of the start of the transaction? I'm not sure it gives that
>type of read-consistent guarantee - it really does give you the current SCN,
>which presumably can change mid-transaction due to other unrelated transactions
>committing. That ought to be easy to test; consider:
>
>SQL> begin
> 2 for i in 1..10 loop
> 3 dbms_output.put_line(dbms_flashback.get_system_change_number);
> 4 dbms_lock.sleep(1);
> 5 end loop;
> 6 end;
> 7 /
>4302410
>4302411
>4302411
>4302411
>4302412
>4302412
>4302412
>4302413
>4302413
>4302413

 Actually it'd be a better demonstration if it shows this happens even after starting a transaction, so:

SQL> insert into test values ('TX3', 0);

1 row inserted

SQL> begin

  2      for i in 1..10 loop
  3          dbms_output.put_line(dbms_flashback.get_system_change_number);
  4          dbms_lock.sleep(1);
  5      end loop;

  6 end;
  7 /

4316672
4316672
4316673
4316673
4316673
4316674
4316674
4316674
4316675
4316675

PL/SQL procedure successfully completed

-- 
Andy Hassall / <andy@andyh.co.uk> / <http://www.andyh.co.uk>
<http://www.andyhsoftware.co.uk/space> Space: disk usage analysis tool

Received on Fri Apr 22 2005 - 17:22:44 CDT