Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question.

Asked: May 14, 2016 - 1:15 pm UTC

Last updated: November 30, 2018 - 6:55 am UTC

Version: 11g

Viewed 10K+ times! This question is

You Asked

How to find a row on a specific inserted date using a scn?
And What is major difference scn and ora_rowscn?

and Connor said...

The SCN is the system change number (SCN) and is like our "clock" - every time we commit, the clock increments. The SCN just marks a consistent point in time in the database.

By default, we record it on the block, so if you change just 1 row in that block, the SCN increments for the block and hence all rows that block. The ORA_ROWSCN lets you see that SCN that is on the block.

If you create the table with the ROWDEPENDENCIES clause, then we take it one step further and record the SCN number with each row. In this case, then each row has its own SCN so you can see the SCN where that particular row got changed rather than just the whole block.

The SCN_TO_TIMESTAMP function can be used to map between the SCN to a point in time.

Hope this helps.

Rating

  (5 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

A reader, May 15, 2016 - 7:29 am UTC

Suppose table created without using rowdependency clause.
Then I want to access those record on a particular table which inserted before one day .
How it is possible?
Connor McDonald
May 16, 2016 - 1:09 am UTC

You will only be able to get this down to the block level (ie, those *blocks* that were last altered before that day)

ramp, May 22, 2016 - 7:23 am UTC

Suppose Table created with rowdependency then what is performance impact or store for both with and without rowdependecy clause.
Connor McDonald
May 23, 2016 - 1:06 am UTC

Performance impact I doubt you could even detect.

You will consume a little more space.

SQL> create table t1 ( x int, y int, z char(50));

Table created.

SQL> create table t2 ( x int, y int, z char(50)) rowdependencies;

Table created.

SQL>
SQL> set timing on
SQL> insert into t1 select rownum, rownum, rownum
  2  from dual
  3  connect by level <= 100000;

100000 rows created.

Elapsed: 00:00:00.22
SQL>
SQL> insert into t2 select rownum, rownum, rownum
  2  from dual
  3  connect by level <= 100000;

100000 rows created.

Elapsed: 00:00:00.14
SQL>
SQL> begin
  2   for i in 1 .. 10000 loop
  3     insert into t1 values (i,i,i);
  4     commit;
  5   end loop;
  6  end;
  7  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.59
SQL>
SQL> begin
  2   for i in 1 .. 10000 loop
  3     insert into t2 values (i,i,i);
  4     commit;
  5   end loop;
  6  end;
  7  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.60
SQL>


every time ?

Sokrates, July 31, 2017 - 7:11 pm UTC

... every time we commit, the clock increments. ...

This is also what Concepts Guide wants to tell us on http://docs.oracle.com/database/122/CNCPT/transactions.htm#CNCPT016 ... The corresponding unique SCN of the transaction ...

But multiple transactions CAN have the same commit SCN, see https://fritshoogland.wordpress.com/2016/10/22/transactions-and-scns/
So concepts guide is wrong ?
Connor McDonald
August 01, 2017 - 2:32 am UTC

*My* comments are perhaps a little flippant, but I dont see that in the Concepts guide.

From the Concepts Guide

"SCNs occur in a monotonically increasing sequence. Oracle Database can use an SCN like a clock because an observed SCN indicates a logical point in time, and repeated observations return equal or greater values. If one event has a lower SCN than another event, then it occurred at an earlier time in the database. Several events may share the same SCN, which means that they occurred at the same time in the database.

Every transaction has an SCN. For example, if a transaction updates a row, then the database records the SCN at which this update occurred. Other modifications in this transaction have the same SCN. When a transaction commits, the database records an SCN for this commit."

None of the above infers uniqueness. Similarly

"The corresponding unique SCN of the transaction is assigned ..."

could/can be interpreted as a transaction only has 1 SCN, not that no other transaction can have it.

so not every time ?

Sokrates, August 01, 2017 - 9:26 am UTC

so the clock is not incremented every time a commit happens ?

index on ORA_ROWSCN

Narendra Gupta, November 29, 2018 - 11:39 am UTC

Thanks for information on SCN. It is really useful.

I have a table having billion of rows and the table is created with ROWDEPENDENCIES.

I want to select records inserted/updated after my previous select.
(I stored the MAX(ORA_ROWSCN) from the previous select and select all records having ORA_ROWSCN greater than that max value)

For this I need index on ORA_ROWSCN. But when I tried to create the index , it says

ORA-43807: indexes on ORA_ROWSCN not allowed.

please suggest solution.
Connor McDonald
November 30, 2018 - 6:55 am UTC

The error sums it up nicely - you can't create an index on that column. ORA_ROWSCN wasn't intended as means of obtaining changes since a point in time. For that you either need

a) a tracking column of your own ("last_modified" etc), OR
b) you log changes (eg using AQ) and then process the queue