How to Check Current System Change Number (SCN)

  • by

System Change Number

Some DBAs will schedule a cron job to capture System Change Number (SCN) periodically to save as a Timestamp/SCN mapping reference. To achieve this goal, you can try the following statements.

SQL> ALTER SESSION SET NLS_TIMESTAMP_TZ_FORMAT = 'YYYY-MM-DD HH24:MI:SS.FF TZH:TZM';

Session altered.

SQL> COLUMN SYSTIMESTAMP FORMAT A35;
SQL> SELECT systimestamp, current_scn FROM v$database;

SYSTIMESTAMP                        CURRENT_SCN
----------------------------------- -----------
2011-07-28 15:56:51.620279 +08:00       3094334

Or use function DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER directly:

SQL> SELECT systimestamp, dbms_flashback.get_system_change_number CURRENT_SCN FROM DUAL;

SYSTIMESTAMP                        CURRENT_SCN
----------------------------------- -----------
2011-07-28 15:57:16.758427 +08:00       3094388

Please note that, the function SYSTIMESTAMP accepts NLS_TIMESTAMP_TZ_FORMAT, not NLS_DATE_FORMAT or NLS_TIMESTAMP_FORMAT.

Further reading: How to Map Timestamp to SCN or Vice Versa

Leave a Reply

Your email address will not be published. Required fields are marked *