[Q]如何知道sequenc的nextval而不用sequencename.nextval

biti_rainy

1：如果有多个进程可能操作这个序列这就是没多大意义的
2：序列不断号几乎是不大可能的，很难保证，包括数据库重新启动（不cache又可能有性能问题）、shared pool  age out 等等因素
3：你的序列看样子increment是5 ？

melocy

http://asktom.oracle.com/pls/ask ... ITERIA:277215805229

yanggq

1.是多个进程操作这个序列，但是不想丢太多，因为曾经发生过sequece out of number 的问题
3.是的，increment 是5.因为需要复制到别的库中，所以本库start with 2, increment by 5.

yanggq

Great!!!
I want to select the current value from a sequence.

When i do : select <seq.name>.currval from dual

ORA-08002 error is appearing.  
sequence %s.CURRVAL is not yet defined in this session

How to avoid this..

If i do : select <>.nextval from dual,  then the value is
changed from the sequence.  After that the currval is working.
But if i disconnect that session and again connect to that
session i am facing the error.

Other ways (Workaround ..):

1)  i can take the value from dba_sequences (last_number-1).

2)  I tried to alter the sequence with nocache.  Eventhough
    it is not working..

Can u help me to solve this issue.

Awaiting for your valuable suggesstion.

Thanking you

With best regards
Ravindar S.



and we said...

Currval is by definition the value returned by your sessions last call to
nextval.  If your session has not called Nextval yet, Currval is undefined.

The value in dba_sequences will typically not suffice to find what the nextval
would be if you selected it as the last_number in dba_sequences jumps by the
cache size.  If you set the NOCACHE option (not recommended, it'll just make
things slow), then last_number will return what nextval would have returned had
you invoked it.

Since on an active system, whatever you get from dba_sequences is "stale" or out
of date almost as soon as you get it (others will be selecting nextval all of
the time) what I recommend is that you use either

  last_number
  last_number-cache_size+1

from dba_sequences.  Either is a good approximation of what the Nextval would be
(and all options to you are approximiations)...


   Reviews      
GOTO a page to Bookmark Review | Bottom | Top
Getting NextVal from Dynamic Performance View  November 17, 2002
Reviewer:  Pablo Rovedo  from Argentina

Tom:

  You can get the nextval through the dynamic performance  
  view sys.v$_sequences.
  
Connecting with sys

sys@ROP816> desc v$_sequences
Name                                                  Null?    Type
----------------------------------------------------- --------
------------------------------
SEQUENCE_OWNER                                                 VARCHAR2(64)
SEQUENCE_NAME                                                  VARCHAR2(1000)
OBJECT#                                                        NUMBER
ACTIVE_FLAG                                                    VARCHAR2(1)
REPLENISH_FLAG                                                 VARCHAR2(1)
WRAP_FLAG                                                      VARCHAR2(1)
NEXTVALUE                                                      NUMBER
MIN_VALUE                                                      NUMBER
MAX_VALUE                                                      NUMBER
INCREMENT_BY                                                   NUMBER
CYCLE_FLAG                                                     VARCHAR2(1)
ORDER_FLAG                                                     VARCHAR2(1)
CACHE_SIZE                                                     NUMBER
HIGHWATER                                                      NUMBER
BACKGROUND_INSTANCE_LOCK                                       VARCHAR2(1)
INSTANCE_LOCK_FLAGS                                            NUMBER

sys@ROP816> create view v$sequences as select * from v$_sequences;

View created.

sys@ROP816> create public synonym v$sequences for sys.v$sequences;

Synonym created.

sys@ROP816> grant select on v$sequences to public;

Grant succeeded.

sys@ROP816>

and then connect with any user

rop@ROP816> create sequence s1_seq;

Sequence created.

rop@ROP816> select s1_seq.nextval from dual;

   NEXTVAL
----------
         1

rop@ROP816> select b.object_name,a.nextvalue
  2  from v$sequences a, user_objects b
  3  where a.object# = b.object_id and
  4        b.object_name = 'S1_SEQ'
  5  /

OBJECT_NAME           NEXTVALUE
-------------------- ----------
S1_SEQ                        2

rop@ROP816> select s1_seq.nextval from dual;

   NEXTVAL
----------
         2

rop@ROP816> select b.object_name,a.nextvalue
  2  from v$sequences a, user_objects b
  3  where a.object# = b.object_id and
  4        b.object_name = 'S1_SEQ'
  5  /

OBJECT_NAME           NEXTVALUE
-------------------- ----------
S1_SEQ                        3

rop@ROP816>

Regards
Pablo A. Rovedo

PD: I have read your book twice, is excellent!!!
    Are you writing a new one? any book regarding     
    performance would by great

yanggq

最初由 melocy 发布
[B]http://asktom.oracle.com/pls/ask ... ITERIA:277215805229 [/B]

谢谢melocy兄

oldwain

可以换个思路：

从使用sequence的业务表中查， 难道不是很方便吗？

yanggq

最初由 oldwain 发布
[B]可以换个思路：

从使用sequence的业务表中查， 难道不是很方便吗？ [/B]

NOT a good idea as I expect.

melocy

要感谢的应该是 Tom