Home » RDBMS Server » Performance Tuning » scn
scn [message #65286] Thu, 22 July 2004 00:06 Go to next message
rashmi kulkarni
Messages: 1
Registered: July 2004
Junior Member
I would like to have the information about SCN.
it will be appreciated if anyone can give this information.
Re: scn [message #65287 is a reply to message #65286] Thu, 22 July 2004 03:58 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
When a user commits a transaction, the transaction is assigned a system change number (SCN), which Oracle records along with the transaction's redo entries in the redo log. SCNs are recorded in the redo log so that recovery operations can be synchronized

more details? please refer documentation
Re: scn [message #65290 is a reply to message #65287] Fri, 23 July 2004 12:38 Go to previous message
prashant
Messages: 122
Registered: September 2000
Senior Member
An SCN or System Change Number is like an internal clock for the database.It is used by the database to guarantee transaction isolation, read consistency, recovery etc.

SCN's are allocated to transactions, upon COMMMIT;

When Oracle starts user X's query -- it says "what is the current system SCN -
oh, it is 55, great". Now every block it reads will be inspected. If the block is newer then scn 55, has any modifications made AFTER scn 55 (scn 55 is like a
time, like "12 oclock noon"), Oracle will roll back the block to scn 55 using the RBS.

All blocks for user X's query will therefore be as of scn 55 -- either because they were not modified after scn 55 or because we rolled them back to scn 55.

There is a transaction array (ITL) on the block header that tells us this.

DBMS_FLASHBACK -- new in 9i, supplies a procedure to get the SCN in place at a specific point in time.

select dbms_flashback.get_system_change_number from dual;

If you have system managed undo then you should try out

SELECT * FROM TABLE AS OF SCN < ... >
AND
SELECT * FROM TABLE AS OF TIMESTAMP < ... >

Which allow you to go back in time and look at changes before they were committed.

If you need to dig deeper into the mysteries of SCN's please ask
Previous Topic: about SCN
Next Topic: plz help me.
Goto Forum:
  


Current Time: Wed Feb 21 13:24:45 CST 2024