How to dump undo segment header

Hi,

Can anyone help me with syntax to dump the undo segment header info to trace??

Oracle 9.0.4

Thanks
Vj

Step 1: Get file number and header block number
select segment_name, header_file, header_block
from dba_segments
where segment_type = 'ROLLBACK';
The result will be something like :
SEGMENT_NAME HEADER_FILE HEADER_BLOCK
-------------------------------- ----------- ------------
SYSTEM 1 2
R01 38 2
R02 38 1822
R03 38 3642
R04 38 5462
R05 38 7282
R06 38 9102
R07 38 10922
R08 38 12742
R09 38 14562
R10 38 16382

Step 2:

ALter system dump datafile 38 block 1822;

If you want to dump R02 rollback segment's header block;

Step 3
Look into dump file in user_dump_dest dir.

Tamil

Thank you

There's much easier and more elegant way available since Oracle 8.0:

Code:

---

alter system dump undo header R02;

---

Thanks Jurji,

Under what circumstances does one need to dump the undo header information?

Badrinath

Quote:

---

Originally posted by badrinathn
Under what circumstances does one need to dump the undo header information?

---

When one is extremely bored and has no better things to do! :p

No seriously, there are some information in undo headers that are sometimes interesting to analyze. In particularly the transaction table stored in undo header might give you interesting information about what is going on in a particular moment of time in a particular rollback segment.

Quote:

---

Originally posted by jmodic
There's much easier and more elegant way available since Oracle 8.0:

Code:

---

alter system dump undo header R02;

---

---

Yes, that is how you dump the transaction table.

If you need to dump an undo block use this syntax:

ALTER SYSTEM DUMP undo block RBS_NAME xid XIDUSN, XIDSLOT, XIDSQN;

Under what circumstances does one need to dump the undo header information?

Assume that you execute a huge transaction ( some thing like 20 M rows update) and later you decide to rollback. Rollback activity may take longer time what you expected. In that case, you dump the undo header block to see the progress of the rollback activity and time it needs to complete (do your math after going the block level details).

Quote:

---

Originally posted by tamilselvan
Under what circumstances does one need to dump the undo header information?

Assume that you execute a huge transaction ( some thing like 20 M rows update) and later you decide to rollback. Rollback activity may take longer time what you expected. In that case, you dump the undo header block to see the progress of the rollback activity and time it needs to complete (do your math after going the block level details).

---

You don't need to dump undo header for that. You can conclude this simply by monitoring the V$TRANSACTION.USED_UBLK decreasing rate. However if the rollback of the transaction is part of the instance recovery, then you indeed need to dump the RBS header to estimate the duration of the transaction rollback - which BTW should not be of so much interest in recent releases of Oracle with delayed transactions crash recovery feature.

(Both tamilselvan's and mine "wisdom" on this subject are courtesy of IXORA, I belive.) http://www.ixora.com.au/scripts/rbs.htm

I have spent some time recently reading Oracle internals. So have Jurji and Tamilselvan I guess, and several other people in this forum.

What made me wonder yesterday is who needs all that knowledge. As far as I have noticed I use not more than 20% of my Oracle knowledge in my everyday tasks.

Do you guys use more than 20-30% in daily work?