Is 8,204,827,991,375 this the actual SCN? This long (large)! Thanks.

 
ops$tkyte@ORA10G> select dbms_flashback.get_system_change_number from dual;
 
GET_SYSTEM_CHANGE_NUMBER
------------------------
              8.2048E+12
 

Hi Tom,

we noticed an interesting behaviour in our database where a table (created with rowdependecies), that only ever gets rows inserted into (no updates), had a row with (what we think) a higher ora_rowscn that originally.

Basically what happens in our system is that the app inserts a row in the parent table (the table in question, lets call it table P) and several corresponding rows in the child (table C) in one transaction. So the parent row and corresponding child rows always have the same ora_rowscn.

We had a node failure in our RAC environment recently and after the recovery, we noticed a mismatch between the ora_rowscn of a row in table P and the ora_rowscn of the corresponding rows in the child table C. The "new" ora_rowscn of the row of table P was higher than the child rows.

We are sure that no manual updates occurred in the parent row. I know that ora_rowscn is generated at commit time but I wonder if instance recovery updates a row with a higher (than the original) ora_rowscn if that row's block is one of the blocks being recovered?

<quote>
Basically what happens in our system is that the app inserts a row in the parent table (the table in question, lets call it table P) and several corresponding rows in the child (table C) in one transaction. So the parent row and
corresponding child rows always have the same ora_rowscn.
</quote>

But actually, the "Sql Reference" says:

"ORA_ROWSCN Pseudocolumn
For each row, ORA_ROWSCN returns the conservative upper bound system change number (SCN) of the most recent change to the row. "

And later:
"NOROWDEPENDENCIES | ROWDEPENDENCIES
This clause lets you specify whether table will use row-level dependency tracking.
With this feature, each row in the table has a system change number (SCN) that
represents a time greater than or equal to the commit time of the last transaction that modified the row."

So you cannot count on the ora_rowscn being equal in the parent and child tables ...

Firstly, sorry I didn't post my name earlier. I realised that it would be difficult to determine whether or not the original poster has replied if there is no name to associate with.

Thanks Tom. I'm getting a hold of the archive redo logs to logmine and check whether or not any manual updates happened.

As for the previous poster Alberto's response below:
<quote>
And later:
"NOROWDEPENDENCIES | ROWDEPENDENCIES
This clause lets you specify whether table will use row-level dependency
tracking.
With this feature, each row in the table has a system change number (SCN) that
represents a time greater than or equal to the commit time of the last
transaction that modified the row."

So you cannot count on the ora_rowscn being equal in the parent and child tables
</quote>

The way I read this was that although the SCN (to be used as ora_rowscn) may be greater than or equal to the commit time of the last transaction, it would still be the same for ALL rows that were modified by a single transaction. That is, Oracle would get hold of an SCN and then use the same SCN to slap it against each of the rows that were modified by that single transaction.

However, of course I may be wrong. Is it true then to say that even if a single transaction is modifying several rows, each of those rows could potentially have different ora_rowscn (say, one higher than the previous)?

Also, in our case, the difference in the ora_rowscn between the parent and the child rows (which were all inserted in a single transaction) is approx 30 mins when I do a scn_to_timestamp query. By the way, there was a RAC node failure in-between the "earlier" ora_rowscn and the "later" ora_rowscn.

So this leads me to question whether or not any instance recovery had any part to play. For example, the above Oracle documentation quote says that the ora_rowscn is >= the commit time of the *last* transaction that modified the row. So in RAC, if a surviving instance performs a recovery for a failed instance by rolling forward changes to a block (which in this case was by inserting new rows), then where will the ora_rowscn for the rolled-forward rows come from? Will it come from redo-logs or will the ora_rowscn be generated based on the *current* transaction's commit time?

> Oracle would get hold of an SCN and then use the same SCN
> to slap it against each of the rows that were modified by
> that single transaction.

No, you may want to read about "block cleanout" and "delayed block cleanout" (eg page 314 of Tom's "Expert Oracle Database Architecture").

Briefly, when Oracle updates the rows, the "tx id" of the tx that modified them is written in the block, and associated with the rows. At commit time, Oracle will get hold of an SCN and associate it with the "tx id", which is physically in the rollback segment header.

"block cleanout" means following the pointer from the row to the tx id in the rollback segment, getting the commit SCN there contained, associating it with the row (thus setting the ora_rowscn), and clearing (actually flagging as reusable) the "tx id" contained in the block.

"block cleanout" happens immediately at commit time for some blocks only (in this case you'll get the exact commit SCN) while the others are cleaned out later ("delayed block cleanout") by the first session the accesses the block (maybe minutes or days or months later). If the "tx id" is still contained in the rollback segment, you'll get the exact SCN as well, but if the "tx id" has been overwritten, Oracle will work out its best approximation - which happens to be always >= than the commit SCN.

Much likely (this is a guess) during recovery, the cleanout is almost always, or very often, "delayed", with an increased probability of seeing an ora_rowscn >= commit SCN.

At the opposite, for small transactions (few blocks modified), it is more likely that all of the blocks modified by the transaction are cleaned out immediately, thus getting an ora_rowscn = commit SCN.

Here's how you can get the SCN for blocks in a 9i table.
(but not the SCN for each row, like you can in 10g)

SELECT 'alter session set tracefile_identifier = '
|| e.SEGMENT_NAME
|| ';'
|| CHR (10)
|| 'alter system dump datafile '
|| file_id
|| ' block min '
|| TO_CHAR (block_id)
|| ' block max '
|| TO_CHAR (block_id + blocks - 1)
|| ';'
"--"
FROM DBA_extents e
WHERE e.tablespace_name = 'USERS'
AND e.SEGMENT_NAME = 'BRIAN'
AND SEGMENT_TYPE = 'TABLE';

Run the generated statements:

alter session set tracefile_identifier = BRIAN;
alter system dump datafile 9 block min 4305 block max 4312;

Review the trace file. Find the scn for each block (in hex)
grep '^scn:' orcl_ora_21718_BRIAN.trc
scn: 0x0708.b3d4a450 seq: 0x02 flg: 0x00 tail: 0xa4502002
etc...

Convert hex to decimal with perl

#!/bin/perl -w

use strict;
use bignum;

my ( $scnHi, $scnLo ) = split ('\.', '0x0708.b3d4a450' );

my $scn = ( (hex $scnHi ) << 32 ) + hex $scnLo;

print "$scn\n";

7733958190160
which agrees well with
select CONTROLFILE_CHANGE# from v$database;
7733958786927