Shulou(Shulou.com)06/01 Report--

Generate a thing update set num=00 where id=1

Looking at the information of things, you can find that things use segment 3 select xid,xidusn,xidslot,xidsqn,ubablk,ubafil from v$transaction.

XID XIDUSN XIDSLOT XIDSQN UBABLK UBAFIL-01001E0007050000 3 30 1287 1566 3

Rollback the segment header block select * from vault rollname; USN NAME-- 0 SYSTEM 1 _ SYSSMU1_3780397527 $2 _ SYSSMU2_2232571081 $3 _ SYSSMU3_2097677531 $4 _ SYSSMU4_1152005954 $5 _ SYSSMU5_1527469038 $6 _ SYSSMU6_2443381498 $7 _ SYSSMU7_3286610060 $8 _ SYSSMU8_2012382730 $9 _ SYSSMU9_1424341975 $10 _ SYSSMU10_3550978943 $

View the segment header id and block number select header_block,header_file from dba_segments where segment_name='_SYSSMU3_2097677531 $'; HEADER_BLOCK HEADER_FILE-1603 of rollback section 3

Dump the rollback segment header to view the process number alter system dump undo header'_ SYSSMU3_2097677531 $'of the current session

Select spid from v$process where addr in (select paddr from v$session where sid= (select sid from v$mystat where rownum=1))

SPID-38380 spid//server process ID 38380

Check the name of trace select * from v$diag_info where name='Default Trace File' INST_ID NAME VALUE- -- 1 Default Trace File / u01/app/oracle/diag/rdbms/hyyk/hyyk/trace/hyyk_ora_38380.trc

View trace's files Next, analyze the first part of less / u01/app/oracle/diag/rdbms/hyyk/hyyk/trace/hyyk_ora_38380.trc piece by piece * Undo Segment: _ SYSSMU3_2097677531 $(3) * Extent Control Header-- -- Extent Header:: spare1: 0 spare2: 0 # extents: 4 # blocks: 271 last map 0x00000000 # maps: 0 offset: 4080 Highwater:: 0x00c00da8 ext#: 3 blk#: 40 ext size: 128 # blocks in seg. Hdr's freelists: 0 # blocks below: 0 mapblk 0x00000000 offset: 3 Unlocked Map Header:: next 0x00000000 # extents: 4 obj#: 0 flag: 0x40000000

# extents: 4 indicates that there are 4 zones in UNDO section 3.

# blocks: 271indicates that there are 271UNDO BLOCK available in the two zones of the 3rd UNDO rollback segment. (the following sql statement 8 "8" 128 "128" 272 is not 272 UNDO BLOCK blocks, because one UNDO segment header block is removed.) ext#: 3 indicates that the transaction occurs in the second zone (starting at 0) blk#: 30 means that the transaction occurs on the 30th block in the second zone. Ext size: 128indicates that there are 128UNDO BLOCK available on the second zone

Through the dba_extents view, it is found that there are 4 zones with a total of 271 blocks select extent_id,file_id,block_id,blocks,bytes from dba_extents where segment_name='_SYSSMU3_2097677531 $'. EXTENT_ID FILE_ID BLOCK_ID BLOCKS BYTES-0 3 160 8 65536 1 3 344 8 65536 2 3 1024 128 1048576 3 3456 128 1048576

Check out the UNDO segment header block through the dba_segments view, that is, the 160th block of file 3 is the UNDO segment header block as the first line of the above statement results 03160 8 65536 select header_file,header_block from dba_segments where segment_name='_SYSSMU3_2097677531 $'

HEADER_FILE HEADER_BLOCK-3 160

Part II: Extent Map-0x00c00081 length: 7 0x00c00088 length: 8 0x00c00280 length: 1280x00c00600 length: 128The map of UNDO rollback section No. 3 has a total of four areas: the first area corresponds to Block 161,162,163,163,162,163,163,162,163,162,163,162,163,163,162,163,162,163,162,163,162,163,162,163,162,163,162,163,163,162,163,162,163,163,162,163,163,162,163,163,163,163,162,163,163,163,162,163,163,162,163,163,163,163,163,162,163,163,163,162,163,163,163,162,163,163,163,162,163,163,163,162,163,163,163,162,163,163,163,16 Blocks 164, 165, 166, 167 The second section of 7 UNDO BLOCK corresponds to block 344, block 10, block 11, block 12, block 13, block 14, block 15, block 16 of document 3, and the third area of UNDO BLOCK corresponds to block 1024 of document 3. The fourth area corresponds to block 3456 of document 3.

The third part, Retention Table-Extent Number:0 Commit Time: 1524377480 Extent Number:1 Commit Time: 1524377480 Extent Number:2 Commit Time: 1524384037 Extent Number:3 Commit Time: 1524384037, the submission timestamp began at midnight on January 1, 1970 (recorded in seconds)

Part IV TRN CTL:: seq: 0x0197 chd: 0x0000 ctl: 0x0013 inc: 0x00000000 nfb: 0x0000 mgc: 0xb000 xts: 0x0068 flg: 0x0001 opt: 2147483646 (0x7ffffffe) uba: 0x00c00da8.0197.2f scn: 0x0000.00201605

Seq: 0x0197 indicates that the UNDOBLOCK block in which the value before the transaction was modified has been overwritten 407 times, corresponding to 0197 in the following uba: uba: 0x00c00da8.0197.2f.

Chd:0x0000 indicates that a new transaction occurs and the new transaction information, that is, the chain header or entry of the transaction table, is placed in the index=0x0000 slot of the TRNTBL:: (transaction table) below. Ctl: 0x0013 represents the chain end of the transaction table (in fact, you can go to TRN TBL:: to see index= 0x0013 (0x013). Its corresponding SCN=0x0000.00201b8b is the largest SCN in this transaction table, that is, the transaction slot will not be overwritten at last)

Nfb: 0x0000 indicates the number of free blocks of UNDO blocks in the free pool, and 0x0000 indicates that there are no free UNDO blocks in the pool, that is, FREE BLOCKPOOL:: has no free blocks.

Flg: 0x0001 indicates the purpose of the block, 1=KTUUNDO HEADER (2=KTU UNDO BLOCK, etc.)

Uba: 0x00c00da8.0197.2f represents the first UNDO record of a new transaction (the address of the undo block consisting of three parts, the number of times the UNDO block is reused, and the number of records in the UNDO block) the address of the undo block: 0x00c00da8 is the 160th block of file 3

Number of times the UNDO block was reused: 0197, that is, the UNDO block was overwritten 407 times

In which article of UNDO block 2f is recorded, that is, in article 36 of UNDO block

0x0000.00201605 represents the smallest submitted SCN in the header block of UNDO paragraph 3. In fact, this SCN is the SCN on the transaction slot corresponding to the smallest SCN in the transaction table

Part 5: free pool of FREE BLOCK POOL:: uba: 0x00000000.0197.2e ext: 0x3 spc: 0xc46 uba: 0x00000000.0197.02 ext: 0x3 spc: 0x1f06 uba: 0x00000000.0197.0b ext: 0x3 spc: 0x15c0 uba: 0x00000000.0091.38 ext: 0x10 spc: 0x546 uba: 0x00000000.0000.00 ext: 0x0 spc: 0x0 UNDO block. When the transaction is committed, the UNDO block in which the transaction is located will be added to the free pool. Uba: the address of the undo block composed of three parts, the number of times the UNDO block is reused, and the record in the article of the UNDO block. When the address of the undo block is 0, it indicates that the UNDO block is not free, that is, where the 0x00000000 ext: UNDO block is located (extent)

Spc: how much free space in the UNDO block (in bytes)

Part VI TRN TBL:: index state cflags wrap# uel scn dba parent-xid nub stmt_num cmt- -0x00 9 0x00 0x05ba 0x0008 0x0000.00201611 0x00c00da5 0x0000.000.00000000 0x00000001 0x00000000 1524399079 0x01 9 0x00 0x05ba 0x000a 0x0000.00201b14 0x00c00da8 0x0000.000.00000000 0x00000001 0x00000000 1524399864 0x02 9 0x00 0x05b8 0x0001 0x0000.00201af3 0x00c00da8 0x0000.000.00000000 0x00000001 0x00000000 1524399845 0x03 9 0x00 0x05b9 0x0018 0x0000.002016dc 0x00c00da5 0x0000.000.00000000 0x00000001 0x00000000 1524399201 0x04 9 0x00 0x05bb 0x0009 0x0000.002018a8 0x00c00da8 0x0000.000.00000000 0x00000001 0x00000000 1524399442 0x05 9 0x00 0x05ba 0x001e 0x0000.00201832 0x00c00da8 0x0000.000.00000000 0x00000001 0x00000000 1524399382 0x06 9 0x00 0x05bc 0x0016 0x0000.00201879 0x00c00da8 0x0000.000.00000000 0x00000001 0x00000000 1524399409 0x07 9 0x00 0x05ba 0x001c 0x0000.00201964 0x00c00da8 0x0000.000.00000000 0x00000001 0x00000000 1524399570 0x08 9 0x00 0x05ba 0x0011 0x0000.0020161f 0x00c00da5 0x0000.000.00000000 0x00000001 0x00000000 1524399079 0x09 9 0x00 0x05bb 0x000b 0x0000.002018e0 0x00c00da8 0x0000.000.00000000 0x00000001 0x00000000 1524399479 0x0a 9 0x00 0x05bb 0x0013 0x0000.00201b39 0x00c00da8 0x0000.000.00000000 0x00000001 0x00000000 1524399875 0x0b 9 0x00 0x05ba 0x001b 0x0000.00201914 0x00c00da8 0x0000.000.00000000 0x00000001 0x00000000 1524399510 0x0c 9 0x00 0x05ba 0x001f 0x0000.0020177e 0x00c00da6 0x0000.000.00000000 0x00000001 0x00000000 1524399305 0x0d 9 0x00 0x05bb 0x0006 0x0000.00201864 0x00c00da8 0x0000.000.00000000 0x00000001 0x00000000 1524399387 0x0e 9 0x00 0x05ba 0x0003 0x0000.002016b7 0x00c00da5 0x0000.000.00000000 0x00000001 0x00000000 1524399173 0x0f 10 0x80 0x05bc 0x0003 0x0000.00201b98 0x00c00da8 0x0000.000.00000000 0x00000001 0x00000000 0 0x10 9 0x00 0x05bc 0x0017 0x0000.00201731 0x00c00da6 0x0000.000.00000000 0x00000001 0x00000000 1524399262 0x11 9 0x00 0x05b9 0x0021 0x0000.00201623 0x00c00da5 0x0000.000.00000000 0x00000001 0x00000000 1524399079 0x12 9 0x00 0x05bb 0x0015 0x0000.00201a68 0x00c00da8 0x0000.000.00000000 0x00000001 0x00000000 1524399753 0x13 9 0x00 0x05bb 0xffff 0x0000.00201b8b 0x00c00da8 0x0000.000.00000000 0x00000001 0x00000000 1524399936 0x14 9 0x00 0x05b9 0x000e 0x0000.0020169c 0x00c00da5 0x0000.000.00000000 0x00000001 0x00000000 1524399143 0x15 9 0x00 0x05ba 0x0002 0x0000.00201a9d 0x00c00da8 0x0000.000.00000000 0x00000001 0x00000000 1524399783 0x16 9 0x00 0x05bb 0x0004 0x0000.0020188f 0x00c00da8 0x0000.000.00000000 0x00000001 0x00000000 1524399419 0x17 9 0x00 0x05ba 0x000c 0x0000.00201750 0x00c00da6 0x0000.000.00000000 0x00000001 0x00000000 1524399266 0x18 9 0x00 0x05b9 0x0010 0x0000.0020170e 0x00c00da6 0x0000.000.00000000 0x00000001 0x00000000 1524399235 0x19 9 0x00 0x05ba 0x0012 0x0000 .00201a39 0x00c00da8 0x0000.000.00000000 0x00000001 0x00000000 1524399722 0x1a 9 0x00 0x05b9 0x0005 0x0000.0020182b 0x00c00da8 0x0000.000.00000000 0x00000002 0x00000000 1524399382 0x1b 9 0x00 0x05ba 0x0007 0x0000.00201936 0x00c00da8 0x0000.000.00000000 0x00000001 0x00000000 1524399540 0x1c 9 0x00 0x05ba 0x001d 0x0000.0020198a 0x00c00da8 0x0000.000.00000000 0x00000001 0x00000000 1524399600 0x1d 9 0x00 0x05ba 0x0019 0x0000.00201a01 0x00c00da8 0x0000.000.00000000 0x00000001 0x00000000 1524399683 0x1e 9 0x00 0x05bb 0x000d 0x0000.00201849 0x00c00da8 0x0000.000.00000000 0x00000001 0x00000000 1524399382 0x1f 9 0x00 0x05bb 0x0020 0x0000.002017b2 0x00c00da6 0x0000.000.00000000 0x00000001 0x00000000 1524399327 0x20 9 0x00 0x05ba 0x001a 0x0000. 002017f8 0x00c00da6 0x0000.000.00000000 0x00000001 0x00000000 1524399378 0x21 9 0x00 0x05b9 0x0014 0x0000.0020162f 0x00c00da5 0x0000.000.00000000 0x00000001 0x00000000 1524399081

The TRN TBL:: (transaction table) is the most important header block of the UNDO segment. Let's explain the meaning of each field one by one: index represents the slot number in the transaction table, which is just a sequence. From the beginning of 0x00 to the end of 0x21, the 11g version has 34 slots. State indicates the transaction status: 9 indicates that the transaction is inactive and 10 indicates that the transaction is active, from which we can see that the transaction on slot 0x17 in hexadecimal is active. Have you noticed that before we have a transaction, Oracle will look for chd=0x0017 in the transaction control list, to put it bluntly, from the slot of index=0x17 to store the latest transaction: note: the following transaction control is before I happen the transaction (that is, do update gyj_test set name='GGGGG' where id=1) Transaction control of the former DUMP) TRN CTL:: seq: 0x000d chd: 0x0017 ctl: 0x000b inc: 0x00000000 nfb:0x0001 mgc: 0xb000 xts: 0x0068 flg: 0x0001 opt: 2147483646 (0x7ffffffe) uba: 0x0280000a.000d.2b scn: 0x0000.0028a26a cflags indicates the status of the transaction in use: 0x00 for inactive transaction, 0x80 for active transaction, 0x10 for dead transaction, 0x90 for rolled back dead transaction, 0x00 for inactive transaction, 0x80 stands for active transaction The latter rarely happens. Wrap# represents the number of times the transaction slot on the transaction table is reused, which is part of XID. 0x001d indicates that the transaction slot was reused 29 times at this time. Uel represents the pointer to the next transaction slot of the current active transaction in the transaction slot (that is, if a new transaction occurs, the index on the transaction slot pointed to by UEL will be used).

Scn represents the SCN that the business starts, commits, and rollback.

Dba represents the uba: the undo block address of the first part, and this DBA is the starting point of the (rollback) rollback, that is, the address of the UNDO block where the last record of the transaction modification was recorded. Nub indicates the number of UNDO blocks used by the current transaction. Cmt says it is closest to the current submission timestamp, which began at midnight on January 1, 1970 (recorded in seconds). 0 indicates that the transaction is active.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.