| checkpoint/SCN [message #59771] |
Sun, 21 December 2003 23:23  |
ora
Messages: 47
Registered: June 2002
|
Member |
|
|
Hello,
Please explain me is there any difference between SCN and checkpoint number/information? If there is no diff then how oracle identify the checkpoint information?
|
|
|
|
| Re: checkpoint/SCN [message #59781 is a reply to message #59771] |
Mon, 22 December 2003 04:43  |
Thiru
Messages: 1089
Registered: May 2002
|
Senior Member |
|
|
SCN = system change number that is assigned(& incremented) everytime someone commits(ie transaction completes) and acts as the internal timestamp for Oracle.
Whenever there is a checkpoint, the CHECKPOINT SCN is incremented and acts as the starting point during instance recovery.
For eg),
-- shows the CHECKPOINT SCN recorded in the datafiles
SQL> select max(CHECKPOINT_CHANGE#) from v$datafile;
MAX(CHECKPOINT_CHANGE#)
-----------------------
789051
-- gets the current system SCN
SQL> select dbms_flashback.get_system_change_number from dual;
GET_SYSTEM_CHANGE_NUMBER
------------------------
789124
-Thiru
|
|
|
|
| Re: checkpoint/SCN [message #59789 is a reply to message #59781] |
Mon, 22 December 2003 19:15 |
Daljit Singh
Messages: 290
Registered: October 2003
Location: Texas
|
Senior Member |
|
|
Hi,
Its ok but i think oracle assign SCN to each block whenever it get updated/dirty weather it is committed or not. and on the basis of this SCN oracle identifies from where to read the block for another transaction means from Rollback segments or from Data blocks.
If i am wrong so plz clarify me Thiru, I am explaining u little more wht i want to say. Suppose user A give an update statement which will update all the records in table and the transanction of user A is not yet committed. All the records have been modified in buffers. And suppose at the time of issuing this update statement the SCN was 50. After that according to u the transaction is not committed so these blocks would still have the SCN of 50. After this a select statement has been fired by user B at this point the SCN is 60 and oracle show him the old data from Undo segments now the question is how oracle identifies that for this transaction rollback should be used. Bcz oracle doc says that transaction will read the data with low or equal SCN of the transaction.
Plz put some light on it. I am little confused on this topic.
|
|
|
|
| Re: checkpoint/SCN [message #59796 is a reply to message #59789] |
Tue, 23 December 2003 04:37 |
Thiru
Messages: 1089
Registered: May 2002
|
Senior Member |
|
|
Daljit,
I should have made it more clear. There are checkpoint SCNs(end of checkpoint),Commit SCNs(end of transaction),Snapshot SCNs(beginning of the query) ,system SCN etc..They are basically sequence of integers Oracle uses internally to keep track of various events and block versions to ensure statement level read consistency,multiversioning,transaction rollback,instance recovery etc etc. They(system SCNs) DO GET incremented as and when you make changes to data blocks irrespective of the state of the transaction. Oracle differentiates between these SCNs depending on its purpose(for example you are concerned about Snapshot SCN when you begin your statement. You are concerned about COMMIT SCN when you commit or rollback.You are concerned about CHECKPOINT SCN when performing recovery and so on )
See the thread mentioned below for a discussion on SCNs with respect to statment level read consistency and the answer to your question. Note that behaviour changes with different isolation level. Default is READ COMMITTED. The SCN is fixed for isolation level of READ ONLY .The snapshot SCN is fixed for isolation level of Serializable ( and the internal sequence numbers are incremented to keep track of its own changes).
When user B issues the Select statement,he will see the data blocks reflected by the Snapshot SCN(60 here). As he proceeds through the blocks, lets say he encounters a block with SCN 70, which means that this has been modified by some other transaction and hence to preserve the statement level read consistency, the query goes to to the rollback segments searching for blocks whose SCN is 60 . The other transactions NEVER see the changes until you commit(ie No Dirty Reads).
-Thiru
[Updated on: Fri, 18 February 2005 23:32] Report message to a moderator |
|
|
|
|
|
| Re: checkpoint/SCN [message #342788 is a reply to message #59771] |
Mon, 25 August 2008 02:02 |
gajini
Messages: 262
Registered: January 2006
|
Senior Member |
|
|
Hi Thiru,
May I know the difference between Checkpoint SCN and System SCN and can you please explain during which situation these SCN values will be used?
Thanks...
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
