How to Find Current SCN in Oracle

DBsGuru 3 mins0
()

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.


Related Articles

, , ,


Loading

How useful was this post?

Click on a star to rate it!

Average rating / 5. Vote count:

No votes so far! Be the first to rate this post.

As you found this post useful...

Follow us on social media!

We are sorry that this post was not useful for you!

Let us improve this post!

Tell us how we can improve this post?

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!

Related Articles