How to Find the Current SCN in Oracle Database Using DBMS_FLASHBACK

How to Find the Current SCN in Oracle Database Using DBMS_FLASHBACK

The System Change Number (SCN) is Oracle's internal logical clock. Every DBA needs to know how to read it. This post shows you the simplest SQL query to get the current SCN from any Oracle Database session, and explains exactly why it matters.

What Is an SCN?

The System Change Number (SCN) is a monotonically increasing integer that Oracle assigns to every committed transaction. It is the foundation of Oracle's consistency and recovery model. Think of it as Oracle's version of a timestamp — except it is more precise and more reliable than wall-clock time. Oracle uses the SCN for:

• Read consistency — your query sees data as it was at the SCN when the query started, never dirty (uncommitted) data
• Crash recovery — Oracle compares SCNs in control files, redo logs, and datafile headers to know where to start recovery
• RMAN backups — backup sets are tagged with SCNs so Oracle knows exactly what has been protected
• Data Guard replication — standby databases use SCNs to stay in sync with the primary
• Flashback operations — you can rewind a table or query to a past SCN to see historical data
• Distributed transactions — SCNs coordinate commits across linked databases

Every COMMIT statement advances the SCN. Oracle also generates SCNs internally for system-level operations.

The Query

1SELECT dbms_flashback.get_system_change_number FROM dual
2/

Code Breakdown

Components of the Query

What the Function Returns

DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER returns a single NUMBER value representing the current SCN at the exact moment the function is called. The value is always positive and always greater than or equal to the previously returned value. It never decreases.

Example output:

1GET_SYSTEM_CHANGE_NUMBER
2------------------------
3            2249659

Key Points

• Package required — DBMS_FLASHBACK is a built-in Oracle-supplied package. It has been available since Oracle 9i.
• Privilege required — The calling user needs EXECUTE privilege on DBMS_FLASHBACK. If you get ORA-00904: invalid identifier, you likely need the DBA to grant this: GRANT EXECUTE ON DBMS_FLASHBACK TO your_user;
• Always increasing — The SCN only goes up. It never resets. This guarantees a total ordering of all transactions.
• Precise to the moment — Two calls made milliseconds apart may return different SCN values because other sessions are committing at the same time.
• No table lock needed — Querying dual with this function is a lightweight, non-blocking read. It is safe to run on a busy production database.
• Works in any Oracle session — You do not need SYSDBA privilege just to read the SCN (only to grant execute, if needed).

Alternative Ways to Get the Current SCN

There are several other methods to retrieve the current SCN. Use the one that fits your privilege level and use case:

1-- Method 2: Using V$DATABASE (requires SELECT on V$DATABASE)
2SELECT current_scn FROM v$database;
3
4-- Method 3: Using TIMESTAMP_TO_SCN (approximation based on sysdate)
5SELECT timestamp_to_scn(SYSDATE) FROM dual;

Tip: V$DATABASE.CURRENT_SCN and DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER may return slightly different values if queried back to back, because the SCN advances between calls. Both are correct snapshots at their respective moments.

DBA Insights and Practical Uses

Capture the SCN Before a Risky Operation

Before running a bulk update, delete, or data load, capture the SCN. If something goes wrong, you can use Flashback Query to see what the data looked like before the change:

 1-- Step 1: Capture the SCN before your change
 2COL x NEW_VALUE saved_scn
 3SELECT dbms_flashback.get_system_change_number x FROM dual;
 4
 5-- Step 2: Run your risky operation
 6DELETE FROM orders WHERE status = 'CANCELLED';
 7COMMIT;
 8
 9-- Step 3: If something went wrong, query data AS OF that SCN
10SELECT * FROM orders AS OF SCN &saved_scn WHERE status = 'CANCELLED';

Use the SCN in Shell Scripts

DBAs frequently capture the SCN in shell scripts to tag backup windows or audit log boundaries:

1CURRENT_SCN=$(sqlplus -S user/pass@db <<EOF
2SET HEADING OFF FEEDBACK OFF
3SELECT dbms_flashback.get_system_change_number FROM dual;
4EXIT;
5EOF
6)
7echo "Backup start SCN: $CURRENT_SCN"

Check SCN Gap in Data Guard

In a Data Guard environment, compare the primary SCN to the standby's applied SCN to measure replication lag in logical terms rather than just seconds.

Important: SCN Headroom

Oracle databases have a maximum SCN limit (the SCN headroom). If your database SCN is advancing unusually fast (caused by bugs or certain configurations), Oracle Support may need to investigate. The DBMS_FLASHBACK function is a simple way to monitor how fast SCN is advancing.

Permissions Quick Reference

1-- Grant execute to a non-DBA user (run as SYSDBA or DBA)
2GRANT EXECUTE ON dbms_flashback TO your_schema_name;
3
4-- Verify the grant
5SELECT grantee, privilege, table_name
6FROM dba_tab_privs
7WHERE table_name = 'DBMS_FLASHBACK';

References

• Oracle Docs — DBMS_FLASHBACK Package Reference (Oracle 18c) — Official Oracle documentation for the DBMS_FLASHBACK package, including the GET_SYSTEM_CHANGE_NUMBER function syntax and return type.
• Oracle Docs — DBMS_FLASHBACK (Oracle 9i) — The original 9i documentation where GET_SYSTEM_CHANGE_NUMBER was introduced, with early usage examples.
• Ask TOM — DBMS_FLASHBACK Usage — Tom Kyte's classic thread on using DBMS_FLASHBACK, including SCN capture patterns for safe data recovery.
• Ask TOM — Explain SCN and ORA_ROWSCN — Explanation of how Oracle uses the SCN as its logical clock for commits and block-level tracking.
• Red Gate Simple Talk — Oracle SCN: An Introduction — Comprehensive introduction to Oracle SCN covering read consistency, crash recovery, RMAN, and Data Guard use cases.
• Oracle Internals by Riyaj — SCN: What, Why, and How — Deep-dive into the internal mechanics of SCN: structure, storage locations (control file, redo logs, datafile headers), and generation.
• Prakash's DBA Blog — Finding the Current SCN of a Database — Practical DBA post comparing multiple methods to retrieve the current SCN in Oracle 10g and 11g.
• Database Journal — Oracle Flashback Query: TIMESTAMP or SCN? — Explains when to use SCN versus TIMESTAMP in flashback queries, with code examples showing both approaches.
• Alec Kaplan DBA Blog — Oracle SCN — Hands-on examples of retrieving SCN using multiple methods, including the ORA-00904 privilege error and its fix.