How to dump undo segment header
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 10 of 10

Thread: How to dump undo segment header

Hybrid View

  1. #1
    Join Date
    Feb 2001
    Posts
    128

    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

  2. #2
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,136
    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

  3. #3
    Join Date
    Feb 2001
    Posts
    128
    Thank you

  4. #4
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    There's much easier and more elegant way available since Oracle 8.0:
    Code:
    alter system dump undo header R02;
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  5. #5
    Join Date
    Jan 2001
    Posts
    642
    Thanks Jurji,

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

    Badrinath
    There is always a better way to do the things.

  6. #6
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    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!

    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.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  7. #7
    Join Date
    Jun 2001
    Location
    Helsinki. Finland
    Posts
    3,938
    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;
    Oracle Certified Master
    Oracle Certified Professional 6i,8i,9i,10g,11g
    email: ocp_9i@yahoo.com

  8. #8
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,136
    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).

  9. #9
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    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
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  10. #10
    Join Date
    Jun 2001
    Location
    Helsinki. Finland
    Posts
    3,938
    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?
    Oracle Certified Master
    Oracle Certified Professional 6i,8i,9i,10g,11g
    email: ocp_9i@yahoo.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width