It is possible to convert SCN (system change number) to date and date to SCN in Oracle databases. This allows us to find out when a specific change in the database occurred.
Query Current SCN in Oracle
We can query the current SCN information in the database as follows.
|
1 2 3 4 5 |
SQL> select to_char(current_scn) from v$database; TO_CHAR(CURRENT_SCN) -------------------------------------------------------------------------------- 1301876678431 |
Convert SCN to Date in Oracle
|
1 2 3 4 5 |
SQL> select scn_to_timestamp(1301876678431) as timestamp from dual; TIMESTAMP --------------------------------------------------------------------------- 28-MAY-19 01.26.38.000000000 PM |
Convert Date to SCN in Oracle
|
1 2 3 4 5 |
SQL> select to_char(timestamp_to_scn(to_timestamp('28-MAY-19 01.26.38.000000000 PM','DD-MON-YY HH:MI:SS:FF9 PM'))) as scn from dual; SCN -------------------------------------------------------------------------------- 1301876678331 |
