what's the internal transaction table for....
Ken Quirici
The internal transaction table for the associated undo tablespace that
the transaction has committed, and the corresponding unique system
change number (SCN) of the transaction is assigned and recorded in the
table.
which the 11g manual corrects to:
The internal transaction table for the associated undo tablespace
RECORDS [my uppercase] that the transaction has committed, etc.
What is this internal transaction table? I can't find reference to it
anywhere else. It seems as though it
would be important in deciding how long to keep rollback segments
around, but I could be wrong there,
since they seem to be kept around forever, or until they get
overwritten, whichever comes first.
Thanks for any illumination!
Mark D Powell
I believe that the material is referring to the Interested Transaction
List, ITL, stored in each table block to keep track of changes by
pointing to the undo segment that contains the undo for the
transaction. The table parameter initrans controls how many of these
areas are pre-allocated to the blocks. Each ITL is 23 bytes in length
plus I believe the ITL is preceeded by a length or usage byte so the
cost is 24 bytes each space wise.
Jonathan Lewis has written in detail on Oracle usage of the ITL. You
can find his web site via a search.
You can see the ITL in a block dump.
HTH -- Mark D Powell --
The Boss
Are you sure it is not referring to X$KTUXE ?
--
Jeroen
Ken Quirici
I think what the documentation I was quoting was referring to was
something
stored in the rollback segments. There was an illustration which
showed
Oracle ensuring query data consistency by getting blocks from rollback
segments with SCN's less than (or equal to except it only illustrated
SCN's less than) what was the current SCN when the query started. It
can only be these internal transaction tables, it seems, that have
this
SCN *in* the rollback segments.
And I also think Oracle 'blindly' overwrites rollback segments when it
gets to the end of allocating new rollback segments up to the amount
specified in the init parameters as max. There appears to be no
intelligence involved in the overwrite or not-overwrite process
except,
if you run out of space, start from the beginning again.
This is much less sophisticated than I had thought it might be. ON the
other hand, it works nearly all the time!
Ken Quirici
One possible place for intelligence in the rollback-segment allocation/
usage
process is the allocation of rollback-segments. How does Oracle know
when
to allocate more rollback-segments, or does it blindly answer all
requests
for a rollback space (i.e. all attempts to write to rollback space)
with a new
segment until it runs out of space, and which point it starts
overwriting?
Mark D Powell
>
> - Show quoted text -
Oracle does not overwrite the end of the rbs (now undo) segment if the
data is still in use. When the data is in use it extends the
segment. The manual explains this fairly well.
Reference the manual name, version, chapter, and topic if using a hard
or local copy of the manual so I or someone else can find the section
you are asking about. If using one of the online versions please post
the link along with enough information to allow finding the exact
section in question.
Oracle keeps origional copies of data in undo. Oracle keeps track of
the undo via the undo segment header blocks and through the ITL enties
in the changed table blocks. Oracle marks changed table blocks with
the SCN of the change so that when the block is accessed Oracle can
determine if it needs to create a read consistent view of the block.
The basic concept is not difficutl but the details get a little
involved. As I posted Jonathan Lewis has discussed undo processing in
detail but if you have questions on the manual material you should put
those to rest before seeking it out.
Ken Quirici
Excellent ref - should keep me busy for awhile - thanks!
Ken Quirici
also apologies for asking before reading; seems to be a bug I caught
somewhere, possibly at birth