Problem solve Get help with specific problems with your technologies, process and projects.

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 last published in March 2007

Dig Deeper on Oracle DBA jobs, training and certification

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.

Start the conversation

Send me notifications when other members comment.

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

Please create a username to comment.