ORA_ROWSCN is
a pseudocolumn of any table which has the most recent change information to a
given row.
Oracle has an ORA_ROWSCN pseudocolumn which reports the last known
change time for a row in a table. The “time” shows a commit SCN number of last
transaction modifying the row, not a real timestamp though. It is important to
note that unless the ROWDEPENDECIES are enabled, then the last SCN is known
only at data block level, not row level, rowscn’s for all rows in a block would
report whatever SCN is in the last change SCN in block header.
ORA_ROWSCN and SCN_TO_TIMESTAMP. Using this ORA_ROWSCN column and SCN_TO_TIMESTAMP function, the last date or timestamp can be found when a table or record updated.
ORA_ROWSCN and SCN_TO_TIMESTAMP. Using this ORA_ROWSCN column and SCN_TO_TIMESTAMP function, the last date or timestamp can be found when a table or record updated.
Here is an example to get the ORA_ROWSCN value when a
row updated.
For each row, ORA_ROWSCN
returns the conservative upper bound system change number (SCN) of the most
recent change to the row. This pseudocolumn is useful for determining
approximately when a row was last updated. It is not absolutely precise,
because Oracle tracks SCNs by transaction committed for the block in which the
row resides.
hum…”not absolutely
precise”. Should I understand “absolutely not precise” ?
Let’s see that.
|
create table test(col1
varchar2(10));
insert into test
values('1');
insert into test
values('2');
select ora_rowscn,
col1 from test;
ORA_ROWSCN COL1
---------- ----------
9.8420E+12 1
|
Let’s try to have a
better display:
|
select to_char(cast(scn_to_timestamp(ora_rowscn)
as date),
'DD/MM/YYYY
HH24:MI:SS') ora_rowscn_date,
col1
from
test;
ORA_ROWSCN_DATE
COL1
-------------------
----------
02/08/2013 16:41:30 1
02/08/2013 16:41:30 2
|
Ok. something different
if I commit?
|
ORA_ROWSCN_DATE
COL1
-------------------
----------
02/08/2013 16:43:30 1
02/08/2013 16:43:30 2
|
Yes. ORA_ROWSCN is set
to insertion SCN first, then to commit SCN.
What about inserting a
new value?
|
insert into test
values('3');
ORA_ROWSCN_DATE
COL1
-------------------
----------
02/08/2013 16:43:30 1
02/08/2013 16:43:30 2
02/08/2013 16:43:30 3
|
Ha? Funny, may last
insertion SCN is the same as the last commited one. Seems to be copied.
Ok. something different
if I commit?
|
ORA_ROWSCN_DATE
COL1
-------------------
----------
02/08/2013 16:46:48 1
02/08/2013 16:46:48 2
02/08/2013 16:46:48 3
|
All records are now
updated with the latest commit SCN.
Looking at the extract
of the doc above, I understand this is because all the records are stored into
the same block. Let’s check that:
|
select to_char(cast(scn_to_timestamp(ora_rowscn)
as date),
'DD/MM/YYYY
HH24:MI:SS') ora_rowscn_date,
col1,
dbms_rowid.rowid_block_number(rowid)
block_number
from
test;
ORA_ROWSCN_DATE
COL1 BLOCK_NUMBER
-------------------
---------- ------------
02/08/2013 16:46:48
1
90625
02/08/2013 16:46:48
2
90625
02/08/2013 16:46:48
3
90625
|
That’s true (it was
obvious with so little number of record :) ).
Let’s see if we have
records scatterred into different blocks:
|
create table test2 as select
rownum col1 from dba_objects where rownum <=3000;
col ora_rowscn_date
for a25
select dbms_rowid.rowid_block_number(rowid)
block_number,
to_char(cast(scn_to_timestamp(ora_rowscn)
as date),
'DD/MM/YYYY
HH24:MI:SS') ora_rowscn_date,
count(*)
from
test2
group by
dbms_rowid.rowid_block_number(rowid),
to_char(cast(scn_to_timestamp(ora_rowscn)
as date),
'DD/MM/YYYY
HH24:MI:SS')
;
BLOCK_NUMBER
ORA_ROWSCN_DATE
COUNT(*)
------------
------------------------- ----------
47481
05/08/2013
15:38:22
658
47484
05/08/2013
15:38:22
658
47485
05/08/2013
15:38:22
368
47482
05/08/2013
15:38:22
658
47483
05/08/2013
15:38:22
658
|
What about inserting a
new value?
|
insert into test2
values (999);
BLOCK_NUMBER
ORA_ROWSCN_DATE
COUNT(*)
------------
------------------------- ----------
47481
05/08/2013
15:38:22
658
47484
05/08/2013
15:38:22
658
47485
05/08/2013
15:38:22
368
47486
05/08/2013
15:41:42
1
47482
05/08/2013
15:38:22
658
47483
05/08/2013
15:38:22
658
|
I can see my new value
stored into a new block whith insertion SCN.
What happen if I commit?
What happen if I commit?
|
BLOCK_NUMBER ORA_ROWSCN_DATE
COUNT(*)
------------
------------------------- ----------
47481
05/08/2013
15:38:22
658
47484
05/08/2013
15:38:22
658
47485
05/08/2013
15:38:22
368
47482
05/08/2013
15:38:22
658
47483
05/08/2013
15:38:22
658
47486
05/08/2013
15:42:18
1
|
I get the commit SCN for
my record.
What about inserting another new record?
What about inserting another new record?
|
insert into test2
values (1000);
BLOCK_NUMBER
ORA_ROWSCN_DATE
COUNT(*)
------------
------------------------- ----------
47481
05/08/2013
15:38:22
658
47484
05/08/2013
15:38:22
658
47485
05/08/2013
15:38:22
368
47482
05/08/2013
15:38:22
658
47483
05/08/2013
15:38:22
658
47486
05/08/2013
15:42:18
2
|
I can see my second new
record stored into the same block as the first one. The SCN did not change.
What happen if I Commit?
What happen if I Commit?
|
BLOCK_NUMBER
ORA_ROWSCN_DATE
COUNT(*)
------------
------------------------- ----------
47481
05/08/2013
15:38:22
658
47484
05/08/2013
15:38:22
658
47485
05/08/2013 15:38:22
368
47482
05/08/2013
15:38:22
658
47483
05/08/2013
15:38:22
658
47486
05/08/2013
15:43:56
2
|
I get the commit SCN for
my two new records in the same block.
What about updating an
existing record?
|
update test2 set col1=9999
where col1=1;
BLOCK_NUMBER
ORA_ROWSCN_DATE
COUNT(*)
------------
------------------------- ----------
47481
05/08/2013
15:38:22
658
47484
05/08/2013
15:38:22
658
47485
05/08/2013
15:38:22
368
47482
05/08/2013
15:38:22
658
47483
05/08/2013
15:38:22
658
47486
05/08/2013
15:43:56
2
|
Nothing happened, no new
SCN. Now I commit.
|
BLOCK_NUMBER
ORA_ROWSCN_DATE
COUNT(*)
------------
------------------------- ----------
47481
05/08/2013
15:45:41
658
47484
05/08/2013
15:38:22
658
47485
05/08/2013
15:38:22
368
47482
05/08/2013
15:38:22
658
47483
05/08/2013
15:38:22
658
47486
05/08/2013
15:43:56
2
|
I can see my commit SCN
for all the records in the same block as the record I updated.
This clearly shows that
the ora_rowscn is tracked at block level.
When inserting data, all
the inserted records get the same SCN as the first inserted record, when they go
into the same block(s), until commit.
When commit happens, all the records in the block(s) where data has been inserted get the same commit SCN.
When commit happens, all the records in the block(s) where data has been inserted get the same commit SCN.
When updating data, all
the updated records do not get their SCN updated, until commit command is ran.
When commit happens, all the records in the same block as the updated record get the commit SCN.
When commit happens, all the records in the same block as the updated record get the commit SCN.
SCN_TO_TIMESTAMP
SCN_TO_TIMESTAMP is a new function, in Oracle 10g, which is used to convert the SCN value generated, using ORA_ROWSCN coumn, into timestamp. SCN_TO_TIMESTAMP takes as an argument a number that evaluates to a system change number (SCN), and returns the approximate timestamp associated with that SCN. The returned value is of TIMESTAMP datatype. This function is useful any time you want to know the timestamp associated with an SCN.
Here we pass the scn value generated in the above query.
SELECT scn_to_timestamp(353845494) FROM emp WHERE empno=7839;
SCN_TO_TIMESTAMP(353845494)
------------------------------------------------
02-SEP-08 03.20.20.000000000 PM
SCN_TO_TIMESTAMP function can also be used in conjunction with the ORA_ROWSCN pseudocolumn to associate a timestamp with the most recent change to a row.
SELECT scn_to_timestamp(ORA_ROWSCN) FROM emp WHERE empno=7839;
SCN_TO_TIMESTAMP(ORA_ROWSCN)
-------------------------------------------------------
02-SEP-08 03.20.20.000000000 PM
No comments:
Post a Comment