DBAsupport.com Forums - Powered by vBulletin
Page 2 of 3 FirstFirst 123 LastLast
Results 11 to 20 of 24

Thread: to find last number generated for sequence object

  1. #11
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Quote Originally Posted by chduarte
    You can avoid the problem by changing the sequences as follows:

    ALTER SEQUENCE XXX NOCACHE;
    I'd say "disable the feature" rathr than "avoid the problem", since in doing so you are potentially ruining your system performance.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  2. #12
    Join Date
    Dec 2006
    Location
    Brazil
    Posts
    36
    Totally agree. Actually "avoid the problem" means avoid the problem with sequence gaps.
    Regards,

    Carlos Duarte
    Oracle Applications DBA

  3. #13
    Join Date
    Feb 2003
    Location
    Leeds, UK
    Posts
    367
    You can avoid the problem by changing the sequences as follows:

    ALTER SEQUENCE XXX NOCACHE;

    Actually "avoid the problem" means avoid the problem with sequence gaps.
    But you cannot avoid the problem; there is no such thing as a gapless sequence. For example, even with nocache set if someone calls seq.nextval and doesn't insert the value somewhere (or does insert it and later rolls back their transaction) that number is gone.

  4. #14
    Join Date
    Dec 2006
    Location
    Brazil
    Posts
    36
    Sure. So let's say, try to minimize gap.
    Regards,

    Carlos Duarte
    Oracle Applications DBA

  5. #15
    Join Date
    May 2005
    Location
    Boracay
    Posts
    681
    u r clever dear
    Behind The Success And Failure Of A Man Is A Woman

  6. #16
    Join Date
    Sep 2000
    Location
    Sao Paulo,SP,Brazil, Earth, Milky Way
    Posts
    350
    Tamilselvan, Carlos : sorry for the delay, but the answer "HIGHWATER" given by Carlos is plain wrong : as said by HJR in the page that I cited (at the bottom of the page, read carefully) the correct answer is NEXTVALUE. My tests in a 9ir2 EE database :

    sid=BDDEV:BDDEV:/u1/app/oracle>sqlplus /nolog

    SQL*Plus: Release 9.2.0.5.0 - Production on Qua Jan 3 14:55:46 2007

    Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

    SQL> conn sys as sysdba
    Enter password:
    Connected.

    ==> user SCOTT have a sequence...

    SQL> select nextvalue, min_value, max_value, cache_size, highwater from v$_sequences
    2 where sequence_name='SEQ_DEPT' and sequence_owner='SCOTT';

    NEXTVALUE MIN_VALUE MAX_VALUE CACHE_SIZE HIGHWATER
    ---------- ---------- ---------- ---------- ----------
    23 1 1,0000E+27 20 41

    ==> lets try to use the sequence (see, the sequence IS cached)...

    scott@BDDEV:SQL>l
    1 select MIN_VALUE, MAX_VALUE, CACHE_SIZE, LAST_NUMBER from user_sequences
    2* where sequence_name='SEQ_DEPT'
    scott@BDDEV:SQL>/

    MIN_VALUE MAX_VALUE CACHE_SIZE LAST_NUMBER
    ------------------ ------------------ ------------------ ------------------
    1 1,000000000000E+27 20 41

    scott@BDDEV:SQL>select SEQ_DEPT.nextval from dual;

    NEXTVAL
    ------------------
    23
    ==> yeah, 23 was the sequence next number, NEXTVAL in the sys object worked... let´s advance it :

    scott@BDDEV:SQL>/

    NEXTVAL
    ------------------
    24

    scott@BDDEV:SQL>/

    NEXTVAL
    ------------------
    25

    scott@BDDEV:SQL>/

    NEXTVAL
    ------------------
    26

    scott@BDDEV:SQL>/

    NEXTVAL
    ------------------
    27

    scott@BDDEV:SQL>/

    NEXTVAL
    ------------------
    28

    scott@BDDEV:SQL>/

    NEXTVAL
    ------------------
    29

    scott@BDDEV:SQL>/

    NEXTVAL
    ------------------
    30

    scott@BDDEV:SQL>/

    NEXTVAL
    ------------------
    31

    scott@BDDEV:SQL>/

    NEXTVAL
    ------------------
    32

    scott@BDDEV:SQL>/

    NEXTVAL
    ------------------
    33

    scott@BDDEV:SQL>/

    NEXTVAL
    ------------------
    34

    scott@BDDEV:SQL>/

    NEXTVAL
    ------------------
    35

    scott@BDDEV:SQL>/

    NEXTVAL
    ------------------
    36

    scott@BDDEV:SQL>/

    NEXTVAL
    ------------------
    37

    scott@BDDEV:SQL>/

    NEXTVAL
    ------------------
    38

    scott@BDDEV:SQL>

    ==> lets see the SYS object again :

    SQL> l
    1 select nextvalue, min_value, max_value, cache_size, highwater from v$_sequences
    2* where sequence_name='SEQ_DEPT' and sequence_owner='SCOTT'
    SQL> /

    NEXTVALUE MIN_VALUE MAX_VALUE CACHE_SIZE HIGHWATER
    ---------- ---------- ---------- ---------- ----------
    39 1 1,0000E+27 20 41

    ==> again correct, 38 was just used, 39 will be the next.... But FOR SURE, don´t trust in sequences for gap free, never...

    []s

    Chiappa

  7. #17
    Join Date
    Dec 2006
    Location
    Brazil
    Posts
    36
    My test:

    create sequence seq_test start with 1 increment by 1 cache 20;
    select seq_test.nextval from dual;
    1

    SEQUENCE_NAME NEXTVALUE MIN_VALUE CACHE_SIZE HIGHWATER
    --------------- ---------- ---------- ---------- ---------
    SEQ_TEST 2 1 20 21

    select seq_test.nextval from dual;
    2

    SEQUENCE_NAME NEXTVALUE MIN_VALUE CACHE_SIZE HIGHWATER
    --------------- ---------- ---------- ---------- ---------
    SEQ_TEST 3 1 20 21

    select seq_test.nextval from dual;
    3

    SEQUENCE_NAME NEXTVALUE MIN_VALUE CACHE_SIZE HIGHWATER
    --------------- ---------- ---------- ---------- ---------
    SEQ_TEST 4 1 20 21

    SQL> shutdown abort;
    SQL> startup;

    select seq_test.nextval from dual
    21

    SEQUENCE_NAME NEXTVALUE MIN_VALUE CACHE_SIZE HIGHWATER
    --------------- ---------- ---------- ---------- ---------
    SEQ_TEST 22 1 20 41


    All numbers in cache have missed.
    Regards,

    Carlos Duarte
    Oracle Applications DBA

  8. #18
    Join Date
    Sep 2000
    Location
    Sao Paulo,SP,Brazil, Earth, Milky Way
    Posts
    350
    Yes, after a SHUTDOWN ABORT (or similar db stops) all in the caches is lost, this is one of the "issues" with it - and at a level, with v$ and the like, consistency do not plays a role here... If it fits the bill, use it, but using the value in NEXTVALUE not in HIGHWATER....

    regards,

    Chiappa

  9. #19
    Join Date
    Dec 2006
    Location
    Brazil
    Posts
    36
    Absolutely agree.
    Regards,

    Carlos Duarte
    Oracle Applications DBA

  10. #20
    Join Date
    Jul 2006
    Posts
    96

    reason for sequence numbers missing??

    hi , why do the sequence numbers are missing if i use
    cache option?
    there is no shutdown abort, nor clearing the shared pool etc...
    is there any possibility at users side i.e application side.??
    if u suggest use NOCACHE option, then how to utilize cache option?

    by madhu

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width