Database Tutorials MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux 
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 |
