Skip to Main Content


Question and Answer

Connor McDonald

Thanks for the question, Mohit.

Asked: May 09, 2024 - 2:26 am UTC

Last updated: May 13, 2024 - 1:40 am UTC

Version: 18

Viewed 1000+ times

You Asked

Hi Tom ,

Not sure about category of this question but will explain the same

We had come across one issue ..

In pre staging server environment sequence column is generating incremented value as per the last record updated in a table via procedure (which is what we want and is fine )

—-issue below
Whereas in production (distributed env)we saw that most recently inserted record has lower sequence value than the previous one for one of the cases.As sequence is incremental it should generate highest value for last updated record.

We are using golden gate in production
Wherein sequence values are taken in odd number for one server and in even numbers for other server .

What could be the scenario ?

Is it beacuse of multiple instances of that server using the same table (distributed server) but that should nt be the issue i guess because server replication should not create wrong data

Note :all commits are in place after dml in a procedure .

Is there any pros/cons of using sequence or cache/no order keyword in Oracle which may be causing this issue ?

Is there a issue with using sequence ?

How to rectify this issue as 100s of procedures are using that sequence functionality?are there any gaps which can be covered while using sequence generated value ?

Kindly confirm

and Connor said...

Sequences are promised to be provided in ascending order, but provided is not the same as committed


session 1 gets sequence 100 at 9:00:00am
session 2 gets sequence 101 at 9:00:01am
session 2 commits
session 1 commits

In the table, sequence 101 appears "before" sequence 100.

Similarly, there is no such thing as a gap free sequence. Any transaction that rolls back will discard the sequence it was using

Here are some options to consider

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library