pigeon7778
asked on
Regarding Sequences
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.
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.
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.
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.
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Doh last_value is not max_value, my apologies.
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
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
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.
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.
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
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
ASKER
Thanks a lot for the information.
last_number is the maximum valid sequence number that will be generated
cace_size I'nve never used - look in the documentation.