Re: System Change Number

Peter wrote:

> When there is a commit Oracle will assign a system change number to
> the redo records of the committed transaction.
> All redo logs will be flushed out from the redo log buffer including
> those of transactions that are not committed.
> How does Oracle identify these uncommitted transactions if they do not
> have a SCN?

Er, precisely because they are missing the SCN?

Actually, the answer is a bit more complex than that, because every transaction gets an SCN, since it's assigned at parse time.

Have a describe of v$logmnr_contents and see what columns are there. Every transaction gets assigned a 'SERIAL#', whether it's committed or not. Every transaction has an 'OPERATION', be it 'UPDATE', 'DELETE', 'INSERT' etc. But there's also an operation called 'COMMIT'.

Were you to do a log file analaysis using logmnr, and you wanted to know which transactions were committed, and which weren't, you would issue the following SQL:

select serial#, sql_redo, operation
from v$logmnr_contents
where serial# in (select serial# from v$logmnr_contents where operation='COMMIT');

That is, you'd match up those occasions when the COMMIT operation matched the DML operations with the same SERIAL#.

And that, roughly speaking, is how Oracle does it, too. When you start a transaction, you get given an SCN. When you commit it, that same SCN is given a commit flag marker. The difference between a committed and uncommitted transaction is simply that those transactions without a commit flag must have been those that weren't committed.

Bear in mind that if you look at a single redo log in isolation, that can give misleading results: you can start a transaction in one log, but committ it after a log switch, so that the commit flag is found in another log. Looking only at the first log, you would think you were uncommitted, but if you only keep reading into the other log, you'll find your commit flag. That's why, during complete recoveries, Oracle keeps reading the redo logs until the End-of-Redo marker is found. That way Oracle can be sure that any transaction that's not been paired off with its commit flag must genuinely have been uncommitted at the point of database closure or failure.

Regards
HJR Received on Mon Sep 29 2003 - 15:11:23 CDT