Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Sequence cache or nocache option

Sequence cache or nocache option

Sequence cache or nocache option


   Why do we give cache or nocache while we
create a sequence.It is compulsory to give
these option.could you give some technical
information about cache and non cache
Tell me what is the default value whether
cache or non cache.

Please explain.

Thanks in advance

RE: Sequence cache or nocache option

From the Oracle 7 Documentation (My comments afterwards):

CACHE specifies how many values of the sequence Oracle7 pre-allocates and keeps in memory for faster access. This integer value can have 28 or less 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; thus, the maximum value allowed for CACHE must be less than the value determined by the following formula:


NOCACHE specifies that values of the sequence are not pre-allocated.

If you omit both the CACHE parameter and the NOCACHE option, Oracle7 caches 20 sequence numbers by default. However, if you are using Oracle7 with the Parallel Server option in parallel mode and you specify the ORDER option, sequence values are never cached, regardless of whether you specify the CACHE parameter or the NOCACHE option.

My Comments:  It looks like the only purpose to using the CACHE command is that it will preassign a set of sequence numbers to be used next, in hopes of speeding the process up.   If NO CACHE is used, when a record is added to a table, a process will be run to find the next sequence number and load it into the field.   With the CACHE option, it just takes one of the preassigned values and loads it in.

If you do not declare CACHE or NO CACHE, it defaults to CACHE and preassigns the next 20 "sequences".   

Terry M. Hoey

RE: Sequence cache or nocache option

Additionally, if you absolutely need sequential numbers from this sequence, don't cache. It will lose all the number in memory if you have a crash, and you will need to  reset the sequence. (You could lose numbers if commits fail, but that is an app issue.)

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close