Oracle SCN - System Change Number

==============================
Oracle SCN - System Change Number
==============================
SCN is an internal time stamp used by Oracle Database. 

Oracle Database uses SCNs to mark the SCN before which all changes are known to be on disk so that recovery avoids applying unnecessary redo. 

The database also uses SCNs to mark the point at which no redo exists for a set of data so that recovery can stop.

SCNs occur in a monotonically increasing sequence. 

Oracle Database can use an SCN like a clock because an observed SCN indicates a logical point in time and repeated observations return equal or greater values. 
If one event has a lower SCN than another event, then it occurred at an earlier time with respect to the database.
Several events may share the same SCN, which means that they occurred at the same time with respect to the database.

Every transaction has an SCN. 

For example, if a transaction updates a row, then the database records the SCN at which this update occurred. 

Other modifications in this transaction have the same SCN. 

When a transaction commits, the database records an SCN for this commit.

Oracle Database increments SCNs in the system global area (SGA). 

When a transaction modifies data, the database writes a new SCN to the undo data segment assigned to the transaction. 

The log writer process then writes the commit record of the transaction immediately to the online redo log. 

The commit record has the unique SCN of the transaction. 

Oracle Database also uses SCNs as part of its instance recovery and media recovery mechanisms.

==============================
At commit time
==============================
A system change number (SCN) is generated for the COMMIT.

The internal transaction table for the associated undo tablespace records that the transaction has committed. 
The corresponding unique SCN of the transaction is assigned and recorded in the transaction table. 

The log writer (LGWR) process writes remaining redo log entries in the redo log buffers to the online redo log and writes the transaction SCN to the online redo log. 

This atomic event constitutes the commit of the transaction.

==============================
How to find current SCN
==============================
Option 1.
SELECT CURRENT_SCN FROM V$DATABASE;
294565574

Option 2.

SELECT TIMESTAMP_TO_SCN(SYSDATE) FROM DUAL;
294565581

Option 3.
SELECT DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER AS SCN FROM DUAL;
ORA-00904: : invalid identifier


oracle@my_server:~>% sqlplus / as sysdba

SQL> GRANT EXECUTE ON DBMS_FLASHBACK to RI_CENTRAL;

Grant succeeded.

sqlplus RI_CENTRAL/RI_CENTRAL@ora_inst
SELECT DBMS_FLASHBACK.get_system_change_number AS SCN FROM DUAL;

       SCN
----------
 294565682

==============================
SCN to Time
==============================
SQL> SELECT SCN_TO_TIMESTAMP(294565682) AS SCN_TIME FROM DUAL;

SCN_TIME
---------------------------------
09-DEC-18 04.34.28.000000000 PM

SQL> SELECT TO_CHAR(SCN_TO_TIMESTAMP(294565682),'YYYYMMDD hh24:mi:ss') AS SCN_TIME FROM DUAL;

SCN_TIME
------------------------------

20181209 16:34:28

==============================
Script to get current SCN

==============================


CURRENT_SCN=`echo -e "set head off term off echo off feed off ver off space 1 linesize 1000\ncol  DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER() format 999999999999999999999999999999999999999\nselect DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER() from dual;" | sqlplus -S $MY_USER/$MY_PASS@$MY_CONNECTSTR`


echo CURRENT_SCN is: $CURRENT_SCN