Thanks KG, for your reply. Well, it whets my appetite for more.
This surprises me. All the documentation I have read so far tells me that
multiple transactions cannot be write to the same rollback segment data
block.
My first question was basically "well, if many transactions can write to an
extent, then when a transaction "wraps" around to the first extent, why
does it not write to the first extent if it has another active
transaction?". I sort of found an answer to this at asktom: "A transaction
can be allocated space in an extent that is also used by other
transactions. The prohibition against entering an extent that has active
update transactions in it applies only when the extent boundaries are
crossed". Now, why is this prohibited?
As to the free extent pool, when do blocks get assigned and deallocated
from this free pool? No pctfree algorithm here??? And if Oracle always
cycles back to the first extent after writing to the last, would this free
extent pool always have a block belonging to the first extent in it? From
your reply, I deduce that the answer to my second question is that , my
transaction could get assigned to any block in the free extent pool. This
block could belong to the 1st extent or the 20th extent. ???
The reasons for this questions are:
I have one huge rollback segment, with extents sized at 500M each, with a
possible maximum of 22 extents. A batch job has been failing with an
"Unable to extend rollback segment" error. I find that there are users
executing select statements across a link when this batch job is running.
This select takes a TX lock on the rollback segment, and uses 1 block of
undo space. The users never commit or rollback this select transactions.
So, if theres one user executing a select and using the first extent of the
rollback segment, then if my batch job started with writing into the 21st
extent, it would only have 2 extents to write to. Thats about 10 Gb of free
space that cannot be used.
Regards
Raj
"K
Gopalakrishnan To: Multiple recipients of list ORACLE-L
<[EMAIL PROTECTED]>
" cc:
<[EMAIL PROTECTED] Subject: RE: Some rollback doubts
o.com>
Sent by:
[EMAIL PROTECTED]
ity.com
06/12/2003
01:55 PM
Please respond
to ORACLE-L
Rajesh:
I am not sure which document you are referring here. If that documentation
says more than one transaction can not use a rollback segment data block
means, I would say the documentation is incorrect in this case. But this
can not happen concurrently. IT can happen serially.
Each rollback segment's header will have something called free extent pool,
which will link at least 5 undo segment blocks which have more than 400
bytes
of free space. These blocks will be used to store undo information for the
subsequent transactions. This 'free extent pool' will be clearly visible
in the segment header dumps.
You can dump the rollback segment header by using the alter system dump
undo header 'undo_segment_name' command. However this free extent pool
just keep 5 undo blocks with free space. I hope this answers both of
your questions..
Best Regards,
K Gopalakrishnan
-----Original Message-----
[EMAIL PROTECTED]
Sent: Thursday, June 12, 2003 10:00 AM
To: Multiple recipients of list ORACLE-L
>From the docs : Many active transactions can write concurrently to a single
rollback segment--even the same extent of a rollback segment; however, each
data block in a rollback segment's extent can contain information for only
a single transaction.
1) Now, when oracle writes to the last extent in the cycle, and sees that
the first extent has an active transaction, it allocates a new extent.
Well, why should it? Cant it write to the first extent?
2) When a new transaction needs to use the rollback segment, and say it
gets allocated to a rollback segment with a few other transactions, how
does it decide which extent to use from the cycle of extents?
Thanks
Raj
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author:
INET: [EMAIL PROTECTED]
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author:
INET: [EMAIL PROTECTED]
Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (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).
