How do I get the SCN corresponding to my COMMIT?

Question

When I commit a transaction in Oracle how can I get the earliest SCN that contains the transactions changes?

Answer 1

Using the ORA_ROWSCN pseudocolumn with CREATE TABLE ... ROWDEPENDENCIES in 10g+ will allow you to see the SCN associated with each row's most recent commit. All the changes you make in a single commit should have the same SCN value.

Answer 2

After you commit, get the current SCN using DBMS_FLASHBACK.

http://download.oracle.com/docs/cd/B28359_01/appdev.111/b28419/d_flashb.htm#i997043

The ORA_ROWSCN concept proposed by Adam isn't 100% safe.

At 10am on June 1st, I do an insert of a thousand records of 1MB each. At 10:30am, I do another similarly sized insert. A At 11am, I commit.

Those inserts have (almost certainly) been written to the database as dirty blocks (ie containing uncommitted changes). They'll have an SCN according to the time they were written, but the records will be marked as locked (and the block stores the id of transaction holding the lock).

On July 15th, I read those blocks. They are still 'dirty' and appear locked. The read consistency mechanism goes to check the transaction and finds it doesn't exist anymore (and hasn't existed for some time). The system therefore knows these records were committed, but can't actually give an accurate record of when they were committed. It cleans the dirty blocks, and sticks an SCN in there. The SCN isn't an accurate time of when they were committed, but the SCN corresponding to the oldest time the DB can apply.