RE: Read Consistency & ITL's

Rajesh,

Other than the Transaction ID,UBA and Commit SCN (or csc.. Cleanout System Change)
there is something called 'lock byte' in the ITL entry. THis gives the details
about the locked (!!) rows. I have dumped few blocks and have an half written note
with me which explains the lock byte and csc behaviors. I will write to you offline
after completing the note once I get some free time.

In a nutshell 'lock byte' and 'csc' is the key here along with the UBA to get the
read consistent view for the multi update (!) block transactions.

(I have an article in my site which gives an overview about the
transactions, locking
and ITL entry. Have a look there if you have not seen that already! http://www.geocities.com/kgkrish/transactions.html)

Coming to second part of your question,

Fixed_tables (views or objects) will not have any information in the dictionary
and their definitions are coming from the rdbms kernel. So they will not have a valid
object# or the associating details with them. So that might be the reason for your
2030 errors (I THink!!)

Best Regards,
K Gopalakrishnan
Bangalore, INDIA

-----Original Message-----
Rajesh.Rao_at_jpmchase.com
Sent: Monday, March 04, 2002 4:53 PM
To: Multiple recipients of list ORACLE-L

Hello Gurus,

As I understand it. In order to provide a read consistent view of the data while reading a data block, Oracle looks at the SCN in the block header, and compares it to the snapshot of the SCN taken when the read commenced. If the Snapshot SCN is less than the SCN in the block header, the query is directed to read from the rollback segments.

For any transaction that modifies a block, the ITL among other things, also stores the commit SCN and the address to the transaction table in the rollback segment. Assume that the block has just one ITL. This ITL can be reused once the transaction is completed. Assume it is. That is, two transactions have performed updates on the block since our read commenced. If so is the case, how does Oracle know which rollback segment to look at? I am assuming it still looks at the ITL, rolls it back, sees that it needs to rollback further, looks at the ITL in the rolled back block, and rollsback further, and so on, until it can reconstruct the data block at an SCN lower than the snapshot SCN. Is that right?

My second question is, what happens if the data block has two ITL's, both marked with SCN's greater than when the read commenced. Which ITL does Oracle look at to get the address of the rollback segment? Is it the one with the least SCN??

And also, my previous question, remains unanswered so far? Why does Oracle not allow one to grant select privileges on the fixed tables to any other user?

Thanks
Raj

--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author:
  INET: Rajesh.Rao_at_jpmchase.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L


(or the name of mailing list you want to be removed from).  You may


also send the HELP command for other information (like subscribing).


_________________________________________________________
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: K Gopalakrishnan
  INET: kaygopal_at_yahoo.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L


(or the name of mailing list you want to be removed from).  You may


also send the HELP command for other information (like subscribing).