Ask the Expert

What is the SCN?

I am not able to understand the concept of SCN (system change number). When is it alloted in redo? Is it alloted to each DML or DDL we fire or to a commited transaction? And what happens in case we roll back the transaction?

    Requires Free Membership to View

The System Change Number is assigned many times through the course of a session. Each DML (including SELECT) statement will get a SCN. And each DDL will get a SCN. The statement receives a SCN when it starts. The assignment does not wait for commit or rollback. The system uses the SCN to know the order the statements were given to the database. A lower SCN occurs at some point in time before a higher SCN. Imagine UserA performs an UPDATE on table EMP. This UPDATE statement is given SCN 15734. Some point in time later, UserB performs a SELECT on table EMP (and UserA has not committed the changes. The SELECT is given SCN 16014. Oracle knows that UserA has not yet committed the change yet this does not stop UserB from querying the table, even though UserA has a lock on the table. Oracle creates a read-consistent image of the data for UserB. Oracle knows that UserB's SQL statement occurred after UserA's UPDATE and uses the SCN's to ensure that UserB sees the correct image of the data. Once UserA commits the transaction, the commit marker (or rollback) is given a SCN of its own. This way, Oracle will know when the commit (or rollback) was given in relation to other SQL statements.

This was first published in March 2007

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to: