Oracle - capture row commit order in a table Is there a way to capture the order in which rows get committed in a table?

Our application has clients that query the table based on a sequence_id column of the table, that is supposed to reflect the commit order, regularly.
The largest sequence_id received in the previous attempt would be used as the filter to get the new rows.

I want to capture the order in which rows became visible to a read operation (select query). This number will be used by the read operation to decide 'upto what point it received data last time' so it will ask for sequence_id > max_sequence_id recevied_last_time to get the new data.

How do we implement the logic of inserting rows with the correct sequence_id to reflect the order of commits? Using the sequence does not seems to guarantee this.

How do rows become available to a select query? I believe it is after a row is committed, not after a row is inserted. Our tests show that order of rows become available for a read operation is different to the sequence values as the sequence.nextval is calculated at the point of insert and not at the point of commit.
It isn't possible to determine the order in which rows are committed.  Either a sequence or a timestamp can allow you to determine the order in which rows are inserted.  But neither will tell you the order in which those transactions were committed.

Since you have a process that is trying subscribe to changes in a table, however, you have a few options that don't require you to identify this order.  You can use Oracle Change Data Capture (CDC) and then have the reading process register as a subscriber.  Or you can use Streams to publish changes to the table and have the reader process act as a custom apply process.

Both CDC and Streams will send the subscriber the data in commit order.  The CDC change table will also store the commit SCN and the commit timestamp if you really want them (though I suspect you don't really care so long as the reader is sent every change in order).

Normal CDC involves the reader process controlling a window that shows one change set at a time, consuming that change set, and then advancing the window.  The reader consumes data in commit order and never needs to know specifically what the SCN of a change is.  You can hit the change tables directly as well to get the actual SCN if you want to complicate the reader's pull process.

Your reader process can hit the change tables, get the SCN from the change table, move it to the sequence_id column of your existing table, and then do whatever else you need to do with the data in the change table to move it into your existing architecture.  It's a bit messy but doable.  Your reader process could also be a Streams consumer and do the same thing (grabbing the SCN from the Streams change record rather than the CDC change table).