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

DBsGuru

Hello and welcome to DBsGuru,

DBsGuru is a group of experienced DBA professionals and serves databases and their related community by providing technical blogs, projects, training.
Technical blogs are the source of vast information not about databases but its related product like middleware, PL/SQL, replication methodology, and so on.

Thanks for the visits!

Share Learn Grow!

dbsguru.com/

How to Find Current SCN in Oracle