| scn [message #65286] |
Thu, 22 July 2004 00:06  |
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 #65290 is a reply to message #65287] |
Fri, 23 July 2004 12:38  |
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
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
