orawin.info

Niall's Oracle Pages – Oracle Opinion since 2004

New Defaults, Old Side Effects

without comments

When 11.2 came out I posted about deferred segment creation at http://orawin.info/blog/2010/04/25/new-features-new-defaults-new-side-effects/ and a couple of odd side effects. Oracle published a Note  1050193.1 that makes the quite extraordinary claim that

Sequences are not guaranteed to generate all consecutive values starting with the ‘START WITH’ value.

It’s absolutely true that sequences don’t guarantee no gaps – but the fact that start with no longer specifies the starting value either is a bit rich in my view.  Anyway Martin Widlake ran into this issue and has blogged about it here today. I posted a quick comment and then was going to re-run his scripts with and without deferred_segment_creation=true on my 11.2 instance. My test script is exactly the same as his (copy and paste is wonderful) but includes an initial SHOW PARAMETER line and the addition of a set serveroutput on line. My results are not the same.

<pre>NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
deferred_segment_creation            boolean     TRUE

Table dropped.

Sequence dropped.

Sequence dropped.

Table created.

Sequence created.

Sequence created.

1 row created.

I have just created male name number 1

PL/SQL procedure successfully completed.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

I created 10 men and last value was 10

PL/SQL procedure successfully completed.

1 row created.

I have just created female name number 1

PL/SQL procedure successfully completed.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

I created 10 women and last value was 10

PL/SQL procedure successfully completed.

S MIN(FORN_ID) MAX(FORN_ID)
- ------------ ------------
M            1           10
F            1           10

SQL>

The difference. This database has been upgraded to 11.2.0.3, however there doesn’t appear to be a reference to a fix for this behaviour in either the 11.2.0.3 bug fix list or that for 11.2.0.2 . I guess the lesson is the same old one, make sure you run your regression tests on all new releases including patch sets and be alert for silent behaviour changes.


Possibly Related Posts:

Written by Niall Litchfield

November 17th, 2011 at 10:52 am

Posted in Uncategorized

Tagged with , ,

Leave a Reply

%d bloggers like this: