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

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.