Re: sequence caches and synonyms

There is one cache for the sequence per instance. All sessions within one instance share that same cache. If the cache size is 20 and the increment size is 1: I do nextVal and get 1, another session in the same instance does nextVal and gets 2. This has always been the case AFAIK.

Synonyms shouldn't have any effect on this - If I refer to the sequence directly or via a synonym that points to it, I will get the same value. If I do nextVal using the direct sequence name and currVal from the synonym, I will get the same value. If I call the synonym nextVal, I will increment the sequence in exactly the same way I would if I referenced the sequence directly.

From what you've described, it sounds like you're just doing nextVal which will give an incremented value for each call - so you obviously will get slightly different values.

What exactly is the code in your package doing? Can you copy and paste your test case so we can see what you're confused about?

Hope this helps,
Andy

On Tue, 6 Nov 2018 at 18:16, Patrice sur GMail <patrice.boivin_at_gmail.com> wrote:

> Strange situation here,
>
> We noticed that if we ask a package to return the next value which would
> be used for a table entry (done using a sequence), we get a number.
>
> If we go through a local synonym to ask the same package to answer the
> same question, we receive a slightly different answer.
>
> Is it possible that there are two caches of sequences values, depending on
> how the package which refers to the sequence is executed? I suppose if two
> users are entering records concurrently in the same table, they each have
> their own set of cached sequence values (?).
>
> We don't think this happened in 11g, it only seems to be happening in our
> 12.2 databases.
>
> I will log an SR as well, just thought I might ask this list first.
>
> -- Patrice
> My profiles: [image: Facebook]
> <http://www.facebook.com/home.php?#!/profile.php?id=100000206805521>[image:
> LinkedIn] <http://ca.linkedin.com/pub/patrice-boivin/a/933/5a9>[image:
> Twitter] <http://www.twitter.com/PatriceBoivin>
>

--
http://www.freelists.org/webpage/oracle-l