?
Solved

Regarding Sequences

Posted on 2005-02-24
9
Medium Priority
?
1,714 Views
Last Modified: 2008-02-01
SQL> desc all_sequences
 Name                                                  Null?    Type
 ----------------------------------------------------- -------- ------------------------------------
 SEQUENCE_OWNER                                        NOT NULL VARCHAR2(30)
 SEQUENCE_NAME                                         NOT NULL VARCHAR2(30)
 MIN_VALUE                                                      NUMBER
 MAX_VALUE                                                      NUMBER
 INCREMENT_BY                                          NOT NULL NUMBER
 CYCLE_FLAG                                                     VARCHAR2(1)
 ORDER_FLAG                                                     VARCHAR2(1)
 CACHE_SIZE                                            NOT NULL NUMBER
 LAST_NUMBER                                           NOT NULL NUMBER

SQL> select * from all_sequences where SEQUENCE_NAME = 'MASTER1';

SEQUENCE_OWNER                 SEQUENCE_NAME                   MIN_VALUE  MAX_VALUE INCREMENT_BY C O CACHE_SIZE
------------------------------ ------------------------------ ---------- ---------- ------------ - - ----------
LAST_NUMBER
-----------
    ISD                       MASTER1                                   1  999999999       1 N N         20
      61785

SQL> SELECT MASTER1.CURRVAL FROM DUAL;
SELECT MASTER1.CURRVAL FROM DUAL
       *
ERROR at line 1:
ORA-08002: sequence MASTER1.CURRVAL is not yet defined in this session


SQL> SELECT MASTER1.NEXTVAL FROM DUAL;

   NEXTVAL
----------
     61785

SQL> SELECT MASTER1.NEXTVAL FROM DUAL;

   NEXTVAL
----------
     61786


1. I tried to display the current value.Its not getting displayed.Also is the LAST_NUMBER reflects the nextvalue that's going to be generated?

2. Also i want to know the purpose of CACHE_SIZE field in all_sequences.

0
Comment
Question by:pigeon7778
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
9 Comments
 
LVL 22

Expert Comment

by:earth man2
ID: 13391618
you cannot call master1.currval until you have called master1.nextval

last_number is the maximum valid sequence number that will be generated

cace_size I'nve never used - look in the documentation.
0
 
LVL 16

Expert Comment

by:Richard Olutola
ID: 13391629
From the result of your query and from my recollection, last number is the next value to be allocated.
Cache_size is the number of sequences stored in memory to improve performance of allocating sequence numbers. So if you shave cache size of 20, you'll always have the next 20 sequences, in whatever increment value, computed and stored in memory.
Hope this helps.
0
 
LVL 4

Expert Comment

by:alexnuijten
ID: 13391688
SQL> create sequence s
  2  /

Sequence created.

SQL> select s.currval from dual
  2  /
select s.currval from dual
       *
ERROR at line 1:
ORA-08002: sequence S.CURRVAL is not yet defined in this session


SQL> select s.nextval from dual
  2  /

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

SQL> select s.currval from dual
  2  /

   CURRVAL
----------
         1

SQL>

Alex
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 22

Accepted Solution

by:
earth man2 earned 100 total points
ID: 13391716
CACHE

Specify how many values of the sequence Oracle preallocates and keeps in memory for faster access. This integer value can have 28 or fewer digits. The minimum value for this parameter is 2. For sequences that cycle, this value must be less than the number of values in the cycle. You cannot cache more values than will fit in a given cycle of sequence numbers. Therefore, the maximum value allowed for CACHE must be less than the value determined by the following formula:

(CEIL (MAXVALUE - MINVALUE)) / ABS (INCREMENT)

If a system failure occurs, all cached sequence values that have not been used in committed DML statements are lost. The potential number of lost values is equal to the value of the CACHE parameter.
Note:

Oracle Corporation recommends using the CACHE setting to enhance performance if you are using sequences in a Real Application Clusters environment.
NOCACHE

Specify NOCACHE to indicate that values of the sequence are not preallocated.

If you omit both CACHE and NOCACHE, Oracle caches 20 sequence numbers by default.
0
 
LVL 22

Expert Comment

by:earth man2
ID: 13391737
Doh last_value is not max_value, my apologies.
0
 
LVL 7

Expert Comment

by:Mehul Shah
ID: 13391792
Until you dont fire sequence.nextval the sequence does not get initiated and so you will always get an error while firing sequence.currval.

The Last_number column will display the number which was generated when last time the sequence.nextval was used.

Caching has its own advantages and disadvantages..

When you set the cache_size, whenever the sequence.nextval will be called for the first time during the session it will cache multiple values into the memory. For eg. if the cache_size =20 than you will have sequence values from 1..20 cached in the memory so for the next call it does not have to query the database again

But on the other end when you shutdown the database all the intermediate cached sequence values will be lost.

Mehul
0
 
LVL 35

Expert Comment

by:Mark Geerlings
ID: 13392082
If a sequence has its cache = 0, then the "last_number" column will display the number which was generated the last time that sequence.nextval was called. If cache is > 0, the "last_number" column will display the highest number that was cached the last time, which is almost always higher than actual value of sequence.nextval.

Caching sequence values has the advantage of providing slightly faster access to the next value, because the values are in RAM then, and Oracle only has to update the SYS.SEQ$ table once per group cached, instead of every time that "nextval" is called, as it does when cache = 0.
0
 
LVL 32

Expert Comment

by:awking00
ID: 13394777
Be careful about last_number. It is not the last number generated by the sequence, but the last number that was cached. See below.

SQL> CREATE SEQUENCE TEST_SEQ;

Sequence created.

SQL> SELECT LAST_NUMBER
  2  FROM USER_SEQUENCES
  3  WHERE SEQUENCE_NAME = 'TEST_SEQ';

LAST_NUMBER
-----------
          1

SQL> SELECT TEST_SEQ.NEXTVAL
  2  FROM DUAL;

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

SQL> SELECT LAST_NUMBER
  2  FROM USER_SEQUENCES
  3  WHERE SEQUENCE_NAME = 'TEST_SEQ';

LAST_NUMBER
-----------
         21
0
 

Author Comment

by:pigeon7778
ID: 13409433
Thanks a lot for the information.
0

Featured Post

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to take different types of Oracle backups using RMAN.
Suggested Courses

801 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question