24 | 06 | 2017
Latest Articles
Popular Articles

Administration

Time and SCN functions in the Oracle Database

User Rating:  / 1
PoorBest 

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]