What Is Scn In Oracle?

An SCN, or System Change Number, is a logical, internal, and monotonically increasing timestamp that Oracle Database uses to track committed changes.

It functions as the database's internal clock, providing a consistent point in time for all transactions and data modifications. SCNs are fundamental to Oracle's core functionality, ensuring data consistency, enabling recovery, and managing data access in a multi-user environment.

Why SCNs are needed

To fully understand the importance of SCNs, consider a multi-user database environment where multiple transactions are occurring simultaneously. Without a consistent, ordered mechanism to track changes, a transaction could see "dirty reads," or uncommitted data from other sessions, violating database consistency. The SCN resolves this problem by providing a unique marker for a committed version of the database at any point in time, ensuring that all users see a consistent view of the data.

How SCNs ensure read consistency

Oracle uses the SCN to enforce its multi-version read consistency model. This model ensures that the data returned by a query is consistent with the state of the database at the exact moment the query began.

Snapshot SCN: When a query begins, Oracle records the current SCN as the "snapshot SCN" for that query.
Data block versioning: Each data block in Oracle contains an SCN in its header, representing the most recent change to that block.
Reconstructing past versions: If a query needs to read a block that has been modified since the query's snapshot SCN, Oracle uses the Undo data segments to reconstruct the block's content as it existed at the time of the snapshot SCN. This process guarantees that a query's results are not affected by other transactions that committed after the query started.

The life of a transaction and SCNs

The SCN plays a critical role throughout a transaction's lifecycle:

Transaction begins: A user-initiated transaction begins with a set of DML statements (e.g., INSERT, UPDATE, DELETE).
Modifications generate redo: As the transaction modifies data blocks in the database buffer cache, it generates corresponding redo entries. These entries record the changes and are assigned an SCN. Multiple changes within the same transaction can share the same SCN.
Transaction commits: When the user issues a COMMIT statement, Oracle performs the following actions:
- New SCN generation: A new, unique commit SCN is generated.
- Redo flush: The Log Writer (LGWR) process immediately writes the redo log buffer, including the commit record and its SCN, to the online redo log files on disk. This is an atomic action that makes the transaction durable.
- Lock release: The transaction's locks are released, making the changes visible to other sessions.

SCNs and database recovery

The SCN is crucial for maintaining the database's integrity during instance or media recovery.

Checkpoints: A checkpoint is an event that forces the Database Writer (DBWn) process to write modified data blocks from the buffer cache to the data files on disk. The checkpoint process updates the SCN in the data file headers and the control file.
Instance recovery: In the event of a crash, Oracle uses the SCN to determine where to start recovery. By comparing the SCNs in the data file headers with the SCNs in the control file, the database can identify which redo log entries need to be applied to roll forward the database to a consistent state.
Media recovery: During media recovery (e.g., after a disk failure), archived redo log files are applied in SCN order to reconstruct the database's state up to a specific point in time.

The SCN structure

Prior to Oracle Database 12c, an SCN was a 6-byte (48-bit) number consisting of two parts:

SCN_WRAP (16 bits): This component is incremented when the SCN_BASE reaches its maximum value.
SCN_BASE (32 bits): This component is the primary incrementing counter.

Beginning with Oracle Database 12c, the SCN is an 8-byte number, providing an even larger range and extending the time before an SCN rollover could occur.

How to see and use SCNs

Database administrators and developers can interact with SCNs using various tools and functions.

View current SCN: You can retrieve the current system SCN by querying the v$database view or using the dbms_flashback.get_system_change_number function.sql
```
-- Using v$database
SELECT current_scn FROM v$database;
-- Using the DBMS_FLASHBACK package
SELECT dbms_flashback.get_system_change_number FROM dual;
```
Use code with caution.
SCN to timestamp mapping: Oracle provides functions to convert SCNs to timestamps and vice-versa, which is essential for certain recovery or data retrieval operations.sql
```
-- Convert SCN to timestamp
SELECT scn_to_timestamp(20959080) FROM dual;
-- Convert timestamp to SCN
SELECT timestamp_to_scn(SYSTIMESTAMP) FROM dual;
```
Use code with caution.
Flashback features: SCNs are the backbone of Oracle's Flashback features, such as Flashback Query, which allows you to query data as it existed at a specific point in time or SCN.sql
```
SELECT * FROM employees AS OF SCN 20959080;
```
Use code with caution.
ORA_ROWSCN: This pseudo-column allows you to see the SCN of the most recent change to a data block or, with the ROWDEPENDENCIES option, to the individual row itself.sql
```
SELECT id, name, ora_rowscn FROM my_table;
```
Use code with caution.

Practical applications of SCN

Data replication: Products like Oracle GoldenGate use SCNs to identify and replicate changes from one database to another, ensuring data is synchronized.
Change data capture (CDC): By tracking SCNs, applications can identify and capture incremental changes to the database for use in data warehousing or other purposes.
Database maintenance: DBAs use SCN information during recovery and cloning operations to ensure consistency across the database files.

Enjoyed this article? Share it with a friend.