Oracle System Change Number (SCN) : How to find the current SCN, SCN and Archive Log

Oracle SCN (System Change Number) is a mechanism to maintain data consistency in Oracle database. A stamp that defines a committed version of a database at a point in time. Oracle assigns every committed transaction a unique SCN.

Checkpoint (CKPT) occurs when all modified database buffers in the Oracle SGA are written out to datafiles by the database writer (DBWn) process. It is a data structure that defines an SCN in the redo thread of a database. Checkpoints are recorded in the control file and each datafile header, and are a crucial element of recovery.

How to find current SCN in the database

Method-I

column current_scn format 99999999999999999999;
select current_scn from v$database; 

Method-II

select to_char(dbms_flashback.get_system_change_number) from dual;

Archive Log and SCN:

col first_change# for 999999999999999999
col next_change# for 999999999999999999
select name, thread#, sequence#, status, first_time, next_time 
from v$archived_log 
where &scn between FIRST_CHANGE# and NEXT_CHANGE#;

SCN and Timestamp:

SQL>select scn_to_timestamp(281840233193) as timestamp from dual;

TIMESTAMP
---------------------------------------------------------------------------
04-MAY-15 10.01.49.000000000 AM
SQL>col scn for 99999999999999999999;
SQL>select timestamp_to_scn(to_timestamp('05/04/2015 10:25:01','MM/DD/YYYY HH24:MI:SS')) as scn from dual;

                  SCN
---------------------
         281842111354
SQL>select timestamp_to_scn(to_timestamp('05042015102501','MMDDYYYYHH24MISS')) scn from dual; 
 SCN
---------------------
         281842111354

Other options are to query smon_scn_time and v$log_history. smon_scn_time is owned by SYS and have data worth of 5 days.