Re: column populated from sequence

From: Bliss <bliss_is_ignorance_at_hotmail.com>
Date: Wed, 28 Nov 2001 01:17:24 +0800
Message-ID: <3c03ca89_2_at_news.tm.net.my>

Hi,

[Quoted] When a number is retrieved from sequence, the next "n" values are cached for [Quoted] faster access. You can see this in the Create Sequence command.... When you [Quoted] select nextval from the sequence, the Last_Number column in all_sequences or [Quoted] 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 [Quoted] 11 till the sequence reaches 11 and then when nextval is selected from the [Quoted] sequence the last_number will be updated to 11 + cache_size.... Hence the [Quoted] last_number does not mean the last value fetched by the sequence, but means the last value cached....

[Quoted] 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'

this will display the comments on the columns of the table...

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