Re: Oracle Bug?

On Sat, 23 Apr 2005 16:36:33 -0700, DA Morgan <damorgan_at_x.washington.edu> wrote:

>I was assuming, obviously incorrectly, that ORA_ROWSCN could be
>used to determine (in a sense) which COMMIT was responsible for
>a specific row.

 This is the first time you've mentioend ORA_ROWSCN. Your example consisted only of inserts using DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER, then selecting the stored values back out. At no point did you use ORA_ROWSCN, which does something somewhat different.

 In fact, it does something a _lot_ more like you seem to be expecting... see below.

>My assumption was that TX1 and TX2 being part of a single transaction
>would have the same SCN.

 The commit that committed them both to the database caused an increase in SCN. But the SCN of the database _at the time of each of the two separate inserts_ does not have to be the same as each other - the second one can legitimately be higher than the first, because another, separate, transaction has committed in the period of time between the two inserts. (In this case - an autonomous transaction called in the middle).

 This is what you are reading with DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER, and hence what you're inserting into the table.

> That the autonomous transaction, acting in a
>sense like another user logging on with a separate session, would have
>its own SCN: Obviously not the case. But why still escapes me.

 The commit causes the SCN to increase. This is what's been demonstrated. A transaction doesn't "have" an SCN, at least not whilst it's uncommitted; only the act of committing it causes the database to increase its SCN; so the _commit_ "has" an SCN. (Aren't these are all fundamental concepts in understanding how redo logs get applied during recovery..?)

 OK, you've now mentioned ORA_ROWSCN. Your use of ROWDEPENDENCIES in the create table statement suddenly makes sense, since this allows Oracle to show at a row level (rather than block level) the SCN when a change to each row was last committed to the database:

 Repeating the test using ORA_ROWSCN gives:

REASON                    SCNNO ORA_ROWSCN
-------------------- ---------- ----------
TX1                     4656937    4656939
ATX                     4656937    4656938
TX2                     4656938    4656939

 Which is what you were expecting in the first place.

 The results are all consistent with everything that's been discussed so far:

(1) The SCNNO column shows the SCN at the time of the INSERT statements.

 (1a) There were no commits between the INSERT of TX1 and ATX - the SCNNO values are therefore equal.

 (1b) There was a COMMIT (in the autonomous transaction) between the INSERT of ATX and TX2. The SCN therefore increases.

(2) The ORA_ROWSCN pseudocolumn shows the SCN of the database immediately AFTER the last COMMIT for the most recent change to each row. In other words, the SCN that was created by the transaction being committed to the database.

 (2a) ATX was committed first; it therefore has the earlier SCN.

 (2b) TX1 and TX2 were committed together after ATX; it has a later SCN.

(3) The SCN at the time of each INSERT is lower than the SCN after the corresponding COMMIT for that row (clearly it has to be!). But the SCNs at the time of each of multiple INSERTs in a transaction do not have to be the same.

-- 
Andy Hassall / <andy@andyh.co.uk> / <http://www.andyh.co.uk>
<http://www.andyhsoftware.co.uk/space> Space: disk usage analysis tool