Q

What is the SCN?

I don't understand the concept of SCN (system change number). Is it alloted to each DML or DDL we fire or to a commited transaction?

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?
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

Dig deeper on Oracle DBA jobs, training and certification

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

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:

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close