Tag Archives: TIMESTAMP_TO_SCN

Check CURRENT SCN of the Oracle Database

Check CURRENT SCN of the Oracle Database

Following are the way to check current SCN in Oracle Database:

1. Fetch the current SCN number in v$database view.

Select CURRENT_SCN from v$database;

SQL> Select CURRENT_SCN from v$database;
CURRENT_SCN
-----------
3208426

2. Get from flashback package.

select dbms_flashback.get_system_change_number from dual;

SQL> select dbms_flashback.get_system_change_number from dual;
GET_SYSTEM_CHANGE_NUMBER
------------------------
3208440

3. Get the SCN from timestamp_to_scn function.

select timestamp_to_scn(sysdate) from dual;

SQL> select timestamp_to_scn(sysdate) from dual;
TIMESTAMP_TO_SCN(SYSDATE)
-------------------------
3208466

Example of TIMESTAMP_TO_SCN & SCN_TO_TIMESTAMP function

Select sysdate, timestamp_to_scn(sysdate) from dual;

SYSDATE TIMESTAMP_TO_SCN(SYSDATE)
------------------- -------------------------
02-04-2019 15:25:05 3208525

Select scn_to_timestamp(3208525) from dual;

SCN_TO_TIMESTAMP(3208525)
---------------------------------------------------------------------------
02-APR-19 03.25.03.000000000 PM

select timestamp_to_scn(to_timestamp('02-04-2019 15:25:05','DD-MM-YYYY HH24:MI:SS')) "SCN" from dual;
SCN
----------
3208525