Administration
Time and SCN functions in the Oracle Database
User Rating: / 1
- Details
-
Parent Category: Articles
-
Created on Sunday, 12 September 2010 20:25
-
Last Updated on Friday, 24 August 2012 17:07
-
Published on Sunday, 12 September 2010 20:25
-
Written by Guy Lambregts
-
Hits: 8494
Time and SCN functions in the Oracle Database
This article is about the below 10G time and SCN functions
1. current_scn
2. scn_to_timestamp
3. timestamp_to_scn
4. ora_rowscn
It is worth to know these functions, more precisely they are useful for
1. cancel based recovery ( recover until scn )
2. flashback database and flashback queries
3. auditing, was this row changed ? when was the latest row change done ?
SQL> select current_scn,systimestamp from v$database;
CURRENT_SCN
-----------
SYSTIMESTAMP
---------------------------------------------------------------------------
9103779
12-SEP-10 06.40.02.018542 PM +02:00
Is the current_scn field in v$database precise ?
SQL> select scn_to_timestamp(current_scn),systimestamp from v$database;
SCN_TO_TIMESTAMP(CURRENT_SCN)
---------------------------------------------------------------------------
SYSTIMESTAMP
---------------------------------------------------------------------------
12-SEP-10 06.40.33.000000000 PM
12-SEP-10 06.40.35.894894 PM +02:00
What was the system change number at 6 o' clock in the morning ?
SQL> select timestamp_to_scn('12-SEP-10 06.00.00 AM') from dual;
TIMESTAMP_TO_SCN('12-SEP-1006.00.00AM')
---------------------------------------
9086544
ORA_ROWSCN is a pseudocolumn which represents the most recent SCN a given row was changed. However if the table was created WITHOUT the attribute ROWDEPENDENCIES then ORA_ROWSCN changes for all the rows which fit in the same block as soon as one of the rows is changed. Only if the table was created with the attribute ROWDEPENDENCIES the ORA_ROWSCN is unique for a given row.
Time for some gym.
SQL> connect pm/PM
Connected.
SQL> create table t_scn (col1 number);
Table created.
SQL> insert into t_scn values (1);
1 row created.
SQL> insert into t_scn values (2);
1 row created.
SQL> commit;
Commit complete.
SQL> select col1,ora_rowscn from t_scn;
COL1 ORA_ROWSCN
---------- ----------
1 9105281
2 9105281
SQL> update t_scn set col1=10 where col1 = 1;
1 row updated.
SQL> commit;
Commit complete.
SQL> select col1,ora_rowscn from t_scn;
COL1 ORA_ROWSCN
---------- ----------
10 9105319
2 9105319 >>>>> ORA_ROWSCN has also changed
We recreate the table with the attribute ROWDEPENDENCIES
SQL> drop table t_scn purge;
Table dropped.
SQL> create table t_scn (col1 number) ROWDEPENDENCIES;
Table created.
SQL> insert into t_scn values (1);
1 row created.
SQL> insert into t_scn values (2);
1 row created.
SQL> commit;
Commit complete.
SQL> select col1,ora_rowscn from t_scn;
COL1 ORA_ROWSCN
---------- ----------
1 9105469
2 9105469
SQL> update t_scn set col1=10 where col1 = 1;
1 row updated.
SQL> commit;
Commit complete.
SQL> select col1,ora_rowscn from t_scn;
COL1 ORA_ROWSCN
---------- ----------
10 9105481
2 9105469 >>> the ORA_ROWSCN did not change, only the first row was changed.
SQL> select col1,ora_rowscn,scn_to_timestamp(ora_rowscn) CHANGED_AT from t_scn;
COL1 ORA_ROWSCN
---------- ----------
CHANGED_AT
---------------------------------------------------------------------------
10 9105481
12-SEP-10 07.15.09.000000000 PM
2 9105469
12-SEP-10 07.14.39.000000000 PM
See also Oracle' s Metalink
How ORA_ROWSCN Works with Rowdependencies and Norowdependecies [ID 805424.1]
ORA_ROWSCN Concurrency Control and Optimistic locking in 10g. [ID 741848.1]