-
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.
-
Totally agree. Actually "avoid the problem" means avoid the problem with sequence gaps.
Regards,
Carlos Duarte
Oracle Applications DBA
-
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.
-
Sure. So let's say, try to minimize gap.
Regards,
Carlos Duarte
Oracle Applications DBA
-
u r clever dear
Behind The Success And Failure Of A Man Is A Woman
-
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
-
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
-
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
-
Regards,
Carlos Duarte
Oracle Applications DBA
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|