Home » SQL & PL/SQL » SQL & PL/SQL » user_sequences table
user_sequences table [message #278675] Mon, 05 November 2007 06:32 Go to next message
mitthu
Messages: 3
Registered: November 2007
Junior Member
Hi

I am trying the following query:

create sequence s start with 1 increment by 1;
Sequence created.

select s.nextval from dual;

NEXTVAL
----------
1
select CACHE_SIZE, LAST_NUMBER from user_sequences where sequence_name = 'S';

CACHE_SIZE shows value 20
LAST_NUMBER shows value 21

1 declare
2 begin
3 execute immediate ('alter sequence s increment by 10');
4* end;
PL/SQL procedure successfully completed.

select s.nextval from dual;

NEXTVAL
----------
11

select CACHE_SIZE, LAST_NUMBER from user_sequences where sequence_name = 'S';

CACHE_SIZE shows value 20
LAST_NUMBER shows value 211

What is the significance of these 2 columns and how do they change?

Thanks
Mitthu
Re: user_sequences table [message #278677 is a reply to message #278675] Mon, 05 November 2007 06:39 Go to previous messageGo to next message
varu123
Messages: 754
Registered: October 2007
Senior Member
http://www.ss64.com/orad/USER_SEQUENCES.html
Re: user_sequences table [message #278679 is a reply to message #278675] Mon, 05 November 2007 06:44 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
cache_size refers to the number of sequence numbers that are cached: you call a sequence once but it reserves N numbers for you. Those cannot be used by any other session. If they are not used, they are lost forever. The default cache is 20.

last_number refers to the last sequence number written to disk.

All this can be found in the Oracle manuals.

MHE
Re: user_sequences table [message #278682 is a reply to message #278679] Mon, 05 November 2007 06:57 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I thought that the cached sequence values were held in memory for general use - I've just run a test on a sequence with a cache of 20 where different sessions have been able to consistently get consecutive values back from the sequence/
Re: user_sequences table [message #278688 is a reply to message #278675] Mon, 05 November 2007 07:05 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

Even my experience is same as that of JRowbottom . Different Session seems to share the same cache at a time for a given sequence.


Any Explanaton ?


Thumbs Up
rajuvan
Re: user_sequences table [message #278692 is a reply to message #278682] Mon, 05 November 2007 07:11 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
Embarassed You're right. It appears that they are simply cached for general use. My bad. I should have known though. The general purpose for caching is performance if I'm not mistaken. The bottom line remains though: it is all in the manuals.

MHE
Re: user_sequences table [message #279270 is a reply to message #278692] Wed, 07 November 2007 10:10 Go to previous message
mitthu
Messages: 3
Registered: November 2007
Junior Member
Thanks a lot guys for your help. Smile
Previous Topic: killing session
Next Topic: Odd behavior with bulk collect
Goto Forum:
  


Current Time: Thu Dec 08 18:38:55 CST 2016

Total time taken to generate the page: 0.05660 seconds