Re: ORA_ROWSCN and ROWDEPENDENCIES

From: DA Morgan <damorgan_at_psoug.org>
Date: Wed, 17 Jan 2007 21:04:06 -0800
Message-ID: <1169096645.243976@bubbleator.drizzle.com>

hpuxrac wrote:

> DA Morgan wrote:

>> Yesterday, I can't find it now, someone wrote that ROWDEPENDENCIES
>> has a granularity of 3 seconds.
>>
>> While that may have been true in the past ... it seems it another bit
>> of advice that has outlived its usefulness.
>>
>> In 10gR2 the following produces a unique SCN for each row.
>>
>> CREATE TABLE t (
>> testcol NUMBER(10))
>> ROWDEPENDENCIES;
>>
>> BEGIN
>> FOR i IN 1 .. 1000 LOOP
>> INSERT INTO t VALUES (i);
>> COMMIT;
>> user_lock.sleep(0.1);
>> END LOOP;
>> END;
>> /

> 
> You seem to be missing out on some pretty basic oracle concepts here.
> 
> Do different rows in a table that are inserted/changed and committed on
> different commits ever share the same SCN?

I don't know why don't you tell me. ;-)

SQL> CREATE TABLE t (

   2 testcol VARCHAR2(3));

Table created.

SQL> BEGIN
   2 FOR i IN 1..10000 LOOP

   3      INSERT INTO t VALUES ('ABC');
   4      COMMIT;

   5 END LOOP;
   6 END;
   7 /

PL/SQL procedure successfully completed.

SQL> SELECT ora_rowscn, COUNT(*)

   2 FROM t
   3 GROUP BY ora_rowscn;

ORA_ROWSCN COUNT(*)
---------- ----------

    2387360        660
    2383612        660
    2389638        660
    2392659        660
    2392761        100
    2386611        660
    2388125        660
    2390406        660
    2382841        660
    2384366        660
    2388896        660
    2391174        660
    2385131        660
    2385880        660
    2382075        660
    2391935        660

16 rows selected.

SQL> Do you have a different explanation?

Anyone interested should drop the above table, recreate it with the ROWDEPENDENCIES keyword and observe the difference.

There may at one time have been a 3 second granularity, I see no evidence for it in 10gR2.

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

Received on Wed Jan 17 2007 - 23:04:06 CST