Under what conditions (if any) can two transactions share an SCN?
To put another way, is there a guarantee that if several DML statements share an SCN they are guaranteed to be part of the same transaction?
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.Sign up to join this community
Two transactions won't share an SCN barring a bug in Oracle.
Exactly how Oracle ensures this varies by version, platform, and configuration settings. For example, back in the Oracle 9.2 days, there was by default a non-zero
max_commit_propagation_delay which meant that a row that was inserted on one node and committed might not be immediately visible to a query on the other node. In that configuration, Oracle could use a Lamport SCN generation algorithm where SCN's were generated in parallel without a need for internode communication. In 10.1 and later, a broadcast on commit strategy (link is to a PPT presentation from Julian Dyke which is excellent but may be bigger than you'd expect) is used instead and
max_commit_propagation_delay defaults to 0 (no idea what happens if you use 10.1 and set a non-default
max_commit_propagation_delay-- that might well flip back to Lamport).