As the transactions occur in the database, the users reading any area that has changed must not be affected from any side effects which can alter their results. These must be either completed in totality or must not be completed at all. More simply: when we start a transaction we initiate a set of changes. Oracle Database assigns every transaction a unique identifier called a transaction ID”. A transaction groups SQL statements so that they are either all committed, which means they are applied to the database, or all rolled back, which means they are undone from the database. “A transaction is a logical, atomic unit of work that contains one or more SQL statements. Although these definitions are well documented, it is worth repeating them here: Transactionįrom the Concepts guide of Oracle online Documentation: In order to investigate SCN, several terms must be understood. So let’s begin! Building blocks for understanding SCN We will discuss these aspects of the SCN: Oracle does it nicely and very accurately through a “timer” of its own, known as the System Change Number, AKA SCN. If necessary, Oracle will rebuild each row to the stable state at instant in time when the query was issued. One of the guarantees of the Oracle database is that “there are no dirty reads of user data”. Indeed, for this scenario, you would probably expect the output to reflect the ‘state of the database’ – the contents of each row – the way it existed at the moment you issued your query. Things would certainly become confusing if your output suddenly showed ‘0’ for the new rows coming out. Suppose, while you are looking at the output, a colleague starts a new pay run and that batch job clears the current calculation summary. Numbers are flowing across the screen, everything is going great. You are in the Payroll group and, with appropriate authority, have issued a query to pull the currently salary calculation of all the employees. SCN- Why do we need it?Īssume it’s the end of the month and its payday.
It’s worth mentioning that this is not and cannot be complete coverage of every detail about SCN. In this article we will learn the nitty-gritty of SCN: what it is, where it is used, and how it works. Oracle System Change Number: An Introduction - Simple Talk Skip to contentįor many people working with Oracle database, SCN (System Change Number) is a topic that interests them greatly – but despite that it’s a fascinating topic, it’s confusing as well.