Re: column populated from sequence

Hi,

When a number is retrieved from sequence, the next "n" values are cached for faster access. You can see this in the Create Sequence command.... When you select nextval from the sequence, the Last_Number column in all_sequences or user_sequences will store the last value cached...

E.g.
SQL> create sequence emp_seq cache 10;

SQL> select emp_seq.nextval from dual;

will display 1

SQL> select sequence_name, cache_size, last_number

            from user_sequences
            where sequence_name = 'EMP_SEQ';

You will see this displays last_number as 11 i.e. 1 (the number just generated) + cache_size. The value in the last_number column will remain as 11 till the sequence reaches 11 and then when nextval is selected from the sequence the last_number will be updated to 11 + cache_size.... Hence the last_number does not mean the last value fetched by the sequence, but means the last value cached....

Hope this helps....

A small piece of unasked info which I think may help you....

Before selecting from system tables (all_sequences etc), you can give SQL> select column_name, comments

            from all_col_comments
            where table_name = 'SYSTEM TABLE NAME YOU WANT TO SEE'

Regards,
Bliss

"Stjepan Brbot" <Stjepan.Brbot_at_ZG.HiNet.HR> wrote in message news:9tuf3h$1ph9$1_at_as201.hinet.hr...
> When I insert the new row in such a table, ID column of table gets new
> value from sequence so I knew what is the last number of sequence, but
> if I execute query "SELECT sequence_name FROM all_sequences WHERE
> last_number=nnn" where nnn is known last number, I get nothing. There is
> no sequence with last number nnn? Am I doing something wrong?
>
> --
>
> Stjepan Brbot <Stjepan.Brbot_at_ZG.HiNet.HR>
>
>
> "Jim Kennedy" <kennedy-family_at_home.com> wrote in message
> news:xQeM7.77973$XJ4.41893655_at_news1.sttln1.wa.home.com...
> > I would look in the trigger on the table specifically the insert
 trigger.
> > Then I would take a look at the names of the sequences to see if
 someone
> > used a reasonable naming convention.
> > Then I would look to see if I could narrow down the choices based upon
 the
> > highest value in the column and the next value in a sequence (assuming
 very
> > few people are using the application at that time.)
> > Jim
> > "Stjepan Brbot" <Stjepan.Brbot_at_ZG.HiNet.HR> wrote in message
> > news:9trrln$e6ig$3_at_as201.hinet.hr...
> > > How to find out from which sequence an ID column in table is
 populated?
> > > Where is the connection?
> > >
> > > --
> > >
> > > Stjepan Brbot <Stjepan.Brbot_at_ZG.HiNet.HR>
> > >
> > >
> > >
> >
> >
>
Received on Tue Nov 27 2001 - 18:17:24 CET