How to Find Current SCN in Oracle

In this acrticle, we will demonstrate different ways to find current SCN in Oracle. Follow the below SQLs to find SCN in Oracle.

1. Using V$DATABASE: Find the current SCN in the database using V$DATABASE.

SQL> COLUMN CURRENT_SCN FORMAT 9999999999999999
SQL> SELECT CURRENT_SCN FROM V$DATABASE;
prompt$$$$$$$**Welcome to DBsGuru!**Share Learn Grow**$$$$$$$
      CURRENT_SCN
-----------------
          2957933

SQL>
$$$$$$$**Welcome to DBsGuru!**Share Learn Grow**$$$$$$$
SQL>

2. Using DBMS_FLASHBACK: Find the current SCN in the database using DBMS_FLASHBACK.

SQL> COLUMN CURRENT_SCN FORMAT 9999999999999999
SQL> SELECT DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER CURRENT_SCN FROM DUAL;
prompt$$$$$$$**Welcome to DBsGuru!**Share Learn Grow**$$$$$$$
      CURRENT_SCN
-----------------
          2957984

SQL>
$$$$$$$**Welcome to DBsGuru!**Share Learn Grow**$$$$$$$
SQL>

3. Using TIMESTAMP_TO_SCN for SYSDATE: Find the current SCN in the database using TIMESTAMP_TO_SCN FOR SYSDATE.

SQL> SELECT SYSDATE, TIMESTAMP_TO_SCN(SYSDATE) FROM DUAL;
prompt$$$$$$$**Welcome to DBsGuru!**Share Learn Grow**$$$$$$$
SYSDATE   TIMESTAMP_TO_SCN(SYSDATE)
--------- -------------------------
26-DEC-22                   2958202

SQL>
$$$$$$$**Welcome to DBsGuru!**Share Learn Grow**$$$$$$$
SQL>

4. Using TIMESTAMP_TO_SCN for a specific timestamp: Find the current SCN in the database using TIMESTAMP_TO_SCN for a specific timestamp.

SQL> SELECT TIMESTAMP_TO_SCN (TO_TIMESTAMP('26-DEC-2022 16.53.33','DD-MM-YYYY HH24:MI:SS')) "TIMESTAMP_SCN" FROM DUAL;
prompt$$$$$$$**Welcome to DBsGuru!**Share Learn Grow**$$$$$$$
TIMESTAMP_SCN
-------------
      2958183

SQL>
$$$$$$$**Welcome to DBsGuru!**Share Learn Grow**$$$$$$$
SQL>

We always encourage the technical person to visit the section SCRIPTS to get more daily usage SQL commands.

Hope so you like this script!
Please share your valuable feedback/comments/subscribe and follow us below and don’t forget to click on the bell icon to get the most recent update. Click here to know more about our pursuit.

DATABASE, ORACLE, ORACLE 19C, SCRIPTS

How to Find Current SCN in Oracle

Related Articles