Oracle: Get Current SCN with DBMS_FLASHBACK

calendar Mar 8, 2026 / · 5 min read · oracle scn flashback dba sql database recovery read consistency admin scripts dba_tab_privs v$database dbms_flashback  ·
Share on: linkedin copy

How to Find the Current SCN in Oracle Database Using DBMS_FLASHBACK

What number marks "now" inside an Oracle database? The System Change Number (SCN) does. One call to DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER returns it from any session that has EXECUTE on the package.

What Is an SCN?

The System Change Number (SCN) is a logical, internal time stamp used by Oracle Database. SCNs occur in a monotonically increasing sequence, and every transaction has one. When a transaction commits, the database records an SCN for that commit. Oracle uses the SCN for:

  • Read consistency — a statement is consistent to the SCN recorded when it began, so it sees only data committed as of that SCN
  • Recovery — Oracle uses SCNs to mark the SCN before which all changes are known to be on disk, so recovery avoids applying unnecessary redo
  • Flashback operations — AS OF SCN returns rows as they existed at the specified SCN

Several events can share the same SCN, which means they occurred at the same time in the database. Repeated observations of the SCN return equal or greater values.

The Query

1SELECT dbms_flashback.get_system_change_number FROM dual
2/

Code Breakdown

Components of the Query

ComponentTypeDescription
SELECTSQL keywordBegins a data retrieval statement
DBMS_FLASHBACKPL/SQL PackageOracle-supplied package for flashback operations
GET_SYSTEM_CHANGE_NUMBERPackage FunctionReturns the current SCN as an Oracle NUMBER datatype
FROM dualPseudo-tableOracle's single-row utility table; used when no real table is needed
/SQL*Plus commandExecutes the preceding SQL block in SQL*Plus or SQL*Plus-compatible tools

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. A later call returns a value equal to or greater than an earlier one. The SCN 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 is documented in the Oracle9i Release 1 manual.
  • Privilege required — The calling user needs the EXECUTE privilege on DBMS_FLASHBACK. If the call fails, ask the DBA to grant it: GRANT EXECUTE ON DBMS_FLASHBACK TO your_user;
  • Never decreasing — Repeated observations of the SCN return equal or greater values.
  • Changes between calls — Two calls made milliseconds apart may return different SCN values because other sessions are committing at the same time.
  • Works in any Oracle session — The documentation requires only the EXECUTE privilege on the package, so you do not need SYSDBA 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
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;

V$DATABASE.CURRENT_SCN is null if the database is not currently open. TIMESTAMP_TO_SCN returns the approximate SCN for a timestamp. The database remembers the SCN-to-timestamp association for a limited period (no less than 120 hours), and the function returns an error if the timestamp is too old.

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"

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

Posts in this series