http://www.itpub.net/thread-978455-2-1.html

> I did this:
> alter system dump undo header '_SYSSMU1$';
> The dumped trace file shows 0x2f transaction slots. Does it mean this rollback segment supports at 
> most this many transactions?

Does the last index i.e. slot number 0x2f mean we can only have 48 (2F in hex + 1) concurrent transactions 
in this undo segment? Metalink thread 177926.999 says (as do many other documents) that the max number 
of concurrent transactions one segment can support is determined by db_block_size. If you dump all 
your undo headers (or simply select max(KTUXESLT), KTUXEUSN from x$ktuxe group by KTUXEUSN order by 2), 
you'll see system undo segment (USN=0) has max slot# 97 and so can support 98 slots (index starts with 
0), and your non-system undo segment can support 48, assuming your db_block_size is 8k.

To use a specific undo segment in AUM, do something like this on a test box:
alter system set "_smu_debug_mode" = 45 scope = memory;
set transaction use rollback segment "_SYSSMU1$";

Test in 10.2.0.1:

48 transactions is the maximum a rollback (undo) segment can support when the db_block_size is 8k. Since 
I don't have UltraEdit, I edited the files the old fashioned way:

alter system set "_smu_debug_mode" = 45 scope = memory;
create table t as select rownum x from (select * from dual connect by 1=1) where rownum <= 100;

cat 1.sql (make sure there's only 1 "1" in the file):
set transaction use rollback segment "_SYSSMU5$";
delete from t where x = 1;

#copy 1.sql to [number].sql
i=2; while (( i <= 48 )); do cp 1.sql $i.sql; let "i+=1"; done

#Change x=1 to x=[a higher number] for each file
i=1; while (( i <= 48 )); do perl -pi -e "s/1/$i/" $i.sql; let "i+=1"; done

cat test.sh:
sqlplus -S yong/yong @1 &
sqlplus -S yong/yong @1 &
...[total 48 lines]...

#Change 1 to line number in the file:
perl -pi -e '$x=$.; s/1/$x/' test.sh

cat test.sh
sqlplus -S yong/yong @1 &
sqlplus -S yong/yong @2 &
sqlplus -S yong/yong @3 &
...

#Run 48 delete from t where x = [different number]
. ./test.sh

select * from v$rollstat where xacts > 0;

I see 48 under xacts for USN 5. Now I do one more delete on a non-interfering row:

SQL> set transaction use rollback segment "_SYSSMU5$";

Transaction set.

SQL> delete from t where x = 60;
delete from t where x = 60
            *
ERROR at line 1:
ORA-01554: out of transaction slots in transaction tables

It would be easier if I created an undo tablespace using 2k block size (even just for the undo tablespace 
i.e. after setting db_2k_cache_size).

Yong Huang



Other notes about undo header dump:

Bug 2277067:
chd: head
ctl: tail
KTU_EBR: enable block [level] recovery
(cflags Oracle8i 0x80 KTU_EBR    = Enable block level recovery )

Note:452662.1:
state=9 means transaction is committed

cmt: commit

Note:249201.1:
Under TRN TBL (transaction table), index is slot#, wrap# is seq#
(undo seg# is obvious)

Bug 1561125:
[state] 10= Active local TX
[cflags] 0x90 = 0x10 y 0x80 :
0x10 KTU_DEAD = Rollback failed on this TX - mark SMON for recover
0x80 KTU_EBR = Enable block level recovery 



Control SCN:
the "scn" on the last line of the "TRN CTL" section, ktuxcscn
(http://www.itpub.net/thread-1282029-1-1.html)
(message #4 of http://www.itpub.net/thread-1287230-1-1.html)


http://freelists.spaces.live.com/blog/cns!56FD1C35B281E6B4!177.entry?wa=wsignin1.0

Trace file from dump undo header:

State

The state value 9 indicates this slot holds not active transaction, 10 means active transaction, the state can be other value, such as for distributed transaction, 2PC force rollback etc.

Wrap#

Sequence number indicates reuse of the slot. This number is incremented as each slot is reused.

Uel

Used to store starting extent of an active transaction, or the next pointer in the list of committed transaction if transaction inactive.

Nub

Hold the number of blocks in the undo chain.

You can query v$resource_limit view to determine the database has how many transactions at peak time. Be careful of distributed query, it produces transaction, even if it doesn.t consume undo block, but every transaction need one transaction table entry, if these query doesn.t be committed or exit normally, it.ll lead to use up all transaction table entries, then no transactions can start if no space extends.

TRN CTL:: seq: 0x0098 chd: 0x0006 ctl: 0x0013 inc: 0x00000000 nfb: 0x0005 mgc: 0x8201 xts: 0x0068 flg: 0x0001 opt: 2147483646 (0x7ffffffe) uba: 0x00800025.0098.01 scn: 0x0000.00293471

Scn

The committed SCN of the transaction rolled out of the transaction table. It means any transaction associated with SCN lower than this value is committed within this rollback segment. It can be used as commit SCN when delayed block cleanout and the slot has been reused.

CHD (commit head)

Head of the chain of the list of committed transactions stored by increasing scn. It indicates this slot will be reused for new transaction first.

CTL (commit tail)

Tail of the chain of the list of committed transaction.

OPT

Value for the parameter optimal. 



Bug 5864364:
fbi: free block [pool] index