By submitting your personal information, you agree that TechTarget and its partners may contact you regarding relevant content, products and special offers.
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.