In addition to Weibo, there is also WeChat
Please pay attention
WeChat public account
Shulou
2025-01-16 Update From: SLTechnology News&Howtos shulou NAV: SLTechnology News&Howtos > Database >
Share
Shulou(Shulou.com)05/31 Report--
Today, I will talk to you about how to understand dump data blocks, which may not be well understood by many people. in order to make you understand better, the editor has summarized the following for you. I hope you can get something according to this article.
1. Dump data file
Dump data files need to know the storage information of the table: data file number (file#) and block number (block#).
The table space, file number, section distribution, and block distribution information of the following query table
Col segment_name for a20
Col tablespace_name for a20
Select segment_name, segment_type, tablespace_name, extent_id, file_id, block_id, blocks, bytes from dba_extents where owner = 'SCOTT' and segment_name =' EMP'
SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME EXTENT_ID FILE_ID BLOCK_ID BLOCKS BYTES
--
EMP TABLE USERS 0 4 144 8 65536
Then look at the block distribution information recorded in the table.
Select dbms_rowid.rowid_block_number (rowid) block#, count (*) block_records from scott.emp group by dbms_rowid.rowid_block_number (rowid) order by block#
BLOCK# BLOCK_RECORDS
--
151 14
It can be seen that the first few blocks at the beginning of the table segment are used to store the segment header information, and the row record data starts from the subsequent block.
Use the following command to dump one or more blocks:
Alter system dump datafile block
Alter system dump datafile block min block max
First take a look at the tracking file corresponding to the current session.
Col trace_file_name for a80
Select a.value | | b.symbol | | c.instance_name | |'_ ora_' | | d.spid | |
'.trc 'trace_file_name
From (select value from v$parameter where name = 'user_dump_dest') a
(select substr (value,-6,1) symbol
From v$parameter
Where name = 'user_dump_dest') b
(select instance_name from v$instance) c
(select spid
From v$session s, v$process p, v$mystat m
Where s.paddr = p.addr
And s.sid = m.sid
And m.statistic# = 0) d
TRACE_FILE_NAME
C:\ oracle\ diag\ rdbms\ mes\ mes\ trace\ mes_ora_3912.trc
Now the dump file block
Alter system dump datafile 4 block 151
View the contents of the trace file
Start dump data blocks tsn: 4 file#:4 minblk 151 maxblk 151
Block dump from cache:
Dump of buffer cache at level 4 for tsn=4, rdba=16777367
Block dump from disk:
Buffer tsn: 4 rdba: 0x01000097
Scn: 0x0000.001ef9e3 seq: 0x01 flg: 0x06 tail: 0xf9e30601
Frmt: 0x02 chkval: 0xd2a5 type: 0x06=trans data
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x000000000D186E00 to 0x000000000D188E00
00D186E00 0000A206 01000097 001EF9E3 06010000 [.]
...
00D188DF0 0101110C 09C20201 15C102FF F9E30601 [.]
Block header dump: 0x01000097
Object id on Block? Y
Seg/obj: 0x11dec csc: 0x00.1ef9cc itc: 2 flg: e typ: 1-DATA
Brn: 0 bdba: 0x1000090 ver: 0x01 opc: 0
Inc: 0 exflg: 0
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x000a.009.0000044c 0x00c00f07.012e.20-- U1 fsc 0x0000.001ef9e3
0x02 0x000a.004.0000044d 0x00c00f07.012e.1c Cmurmuri-0 scn 0x0000.001ef98a
The main information of the block is as follows:
Tsn: tablespace number
File#: file number
Minblk and maxblk: range of export block numbers
Rdba: relative block address information
Here rdba lists the hexadecimal and decimal forms, and this address can be converted by file number and block number using the following query
Select dbms_utility.make_data_block_address (4151) from dual
DBMS_UTILITY.MAKE_DATA_BLOCK_ADDRESS (4151)
16777367
Its hexadecimal form
Select to_char (16777367, 'xxxxxxxx') from dual
TO_CHAR (1
-
1000097
Conversely, the file number and block number can be obtained by block address translation.
Select dbms_utility.data_block_address_file (16777367) file#, dbms_utility.data_block_address_block (16777367) block# from dual
FILE# BLOCK#
--
4 151
Scn: SCN information of the data block
Tail: consists of the last two bytes of SCN, block type type, and sequence number seq
Oracle uses the tail of the data block to judge the consistency of the content of the data block. If the decomposed tail here does not match the last two bytes, block type and sequence number of SCN, Oracle can determine that the block is in an inconsistent state and needs to be restored. Although the tail information here is displayed at the beginning of the block trace file, in fact it is physically stored at the end of the data block, which is the origin of the tail name.
Valid values for block type type are as follows:
0x02:undo block
0x06:trans data
0x0e:undo segment header
0x10:data segment header block
0x17:bitmapped data segment header
0x20:first level bitmap block
0x21:second level bitmap block
0x23:pagetable segment header
Object id on Block? Y: whether the database objects stored on the data block exist in the sys.obj$ data dictionary
Seg/obj: ID information of database objects, which can be verified by the following query
Col object_name for a30
Select owner, object_name from dba_objects where object_id = to_number ('11declines,' xxxxx')
OWNER OBJECT_NAME
SCOTT EMP
Csc: the SCN (cleanout SCN) when the block is cleaned, and watch to see if it matches the SCN of the block.
Itc: the number of transaction slots (ITL slot). The following two lines are the information of ITL, corresponding to two transactions, identified by xid.
Flag: identifies whether the data block exists in the freelist of the segment, "-" indicates that the block is not in freelist, and "o" represents on, indicating that the block exists in freelist and can be used for insert records.
The following is the header section
Data_block_dump,data header at 0xd186e64
=
Tsiz: 0x1f98
Hsiz: 0x2e
Pbl: 0x0d186e64
76543210
Flag=-
Ntab=1
Nrow=14
Frre=-1
Fsbo=0x2e
Fseo=0x1d61
Avsp=0x1d33
Tosp=0x1d33
0xe:pti [0] nrow=14 offs=0
0x12:pri [0] offs=0x1f72
0x14:pri [1] offs=0x1f47
0x16:pri [2] offs=0x1f1c
0x18:pri [3] offs=0x1ef3
0x1a:pri [4] offs=0x1ec6
0x1c:pri [5] offs=0x1e9d
0x1e:pri [6] offs=0x1e74
0x20:pri [7] offs=0x1e4c
0x22:pri [8] offs=0x1e26
0x24:pri [9] offs=0x1dfb
0x26:pri [10] offs=0x1dd5
0x28:pri [11] offs=0x1daf
0x2a:pri [12] offs=0x1d88
0x2c:pri [13] offs=0x1d61
The meaning here is as follows:
Tsiz: the total space used by the data block to store the data portion (total data area size)
Hsiz: head size (header size)
Pbl: the address pointer (pointer to buffer holding the block) of the block in the cache, and the following line is its block address bdba.
Ntab:number of tables, if the value is greater than 1, the data block belongs to the storage block of cluster.
Nrow:number of rows, the number of records stored in the block.
Fsbo, fseo:free space begin offset and free space end offset, both give the starting position of the free space in the block.
Avsp:available space in the block
Tosp:total available space when all transactions commit
Then there is the line record information, and here is the first record.
Block_row_dump:
Tab 0, row 0, @ 0x1f72
Tl: 38 fb:-- Hmurf FLmuri-lb: 0x1 cc: 8
Col 0: [3] c2 4a 46
Col 1: [5] 53 4d 49 54 48
Col 2: [5] 43 4c 45 52 4b
Col 3: [3] c2 50 03
Col 4: [7] 77 b4 0c 11 01 01 01
Col 5: [2] c2 09
Col 6: * NULL*
Col 7: [2] c1 15
The meaning here is as follows:
Tab 0, row 0, @ 0x1f72: the identity, row identity, and row address of the table in cluster.
Tl: indicates the total byte space occupied by the row, expressed in decimal, and contains the other overhead of the row.
Fb: row tag, H for head of row,F and L for row first piece and last piece, respectively, indicating that this row involves the exported data block, there is no row link, and because there is a row header in the block, there is also row migration.
Lb:ITL transaction slot number
Cc: number of columns
Col n: [k]: data of column n + 1, occupying k bytes.
The following verifies that the second column of data col 1: [5] 53 4d 49 54 48 is given in hexadecimal ASCII code and converted to characters by the following query
Select chr (to_number ('53, 'xx')) | | chr (to_number (' 418, 'xx')) | | chr (to_number (' 49, 'xx')) | | chr (to_number (' 54, 'xx')) | | chr (to_number (' 48, 'xx')) from dual
CHR (TO_NUM
-
SMITH
Or use the following package conversion
Set serveroutput on
Declare c varchar2 (30)
Begin
Dbms_stats.convert_raw_value ('534d495448, c)
Dbms_output.put_line (c)
End
/
SMITH
For the first column of data col 0: [3] c24a 46, this column is of type number and can be converted to a decimal number through the above package
Set serveroutput on
Declare n number
Begin
Dbms_stats.convert_raw_value ('c24a46, n)
Dbms_output.put_line (n)
End
/
7369
You can query and verify from the scott.emp table that the employee numbered 7369 is smith.
For the fifth column of data col 4: [7] 77 b4 0c 11 01 01 01, this column is of type date and can also be converted to a displayable date through the above package
Set serveroutput on
Declare dt date
Begin
Dbms_stats.convert_raw_value ('77b40c11010101, dt)
Dbms_output.put_line (to_char (dt, 'yyyy-mm-dd hh34:mi:ss'))
End
/
1980-12-17 00:00:00
II. Dump index file
The storage of index data blocks is significantly different from that of table data blocks. The storage data blocks corresponding to an b-tree index are branch node blocks (branch block) and leaf node blocks (leaf block). To export an b-tree index, you need to provide the object_id of the index. To do this, query the index object and its ID information.
Col table_name for a30
Col tablespace_name for a20
Col index_name for a20
Col index_type for a10
Select table_name, index_name, index_type, status, tablespace_name from dba_indexes where owner='CMES' and table_name='C_MATERIAL_T'
TABLE_NAME INDEX_NAME INDEX_TYPE STATUS TABLESPACE_NAME
C_MATERIAL_T IDX_FK_MATERIAL_NO NORMAL VALID CMES
C_MATERIAL_T IDX_FK_PART_NO NORMAL VALID CMES
C_MATERIAL_T IDX_PK_MATERIAL_ID NORMAL VALID CMES
Select object_id from dba_objects where object_name = 'IDX_FK_PART_NO'
OBJECT_ID
-
77043
Enable treedump event tracking to export, substituting the object_id of the indexed object into the following command
Alter session set events' immediate trace name treedump level 77043'
View the exported file
-begin tree dump
Branch: 0x14003b3 20972467 (0: nrow: 2, level: 1)
Leaf: 0x14003b4 20972468 (- 1: nrow: 322 rrow: 322)
Leaf: 0x14003b5 20972469 (0: nrow: 23 rrow: 23)
-end tree dump
This index tree has only one branch node (branch), that is, the root node of the index (root), the block address is 0x14003b3, its decimal number is 20972467, and the level of the root node is 1, indicating that the height of the b-tree index is 2, because the level of the index tree is calculated from 0. There are no more branches under the root node, that is, the leaf node block, and the level of the leaf node is always 0, so there is no need to mark it in the dump file. The location of the branches and leaves starts at-1 on the far left instead of 0. For leaf node blocks, nrow represents the number of row pointers stored in the node block, and for branch node blocks, nrow represents the number of blocks pointing to the next layer node. In the leaf node block, there is also rrow, which is related to the transaction on the block (rrow is the number of rows after all current transactions have been committed), and rrow and nrow are equal, indicating that there is no transaction in progress on the data involved in the block.
Analyze the first leaf block on the left, leaf: 0x14003b4 20972468 (- 1: nrow: 322 rrow: 322), and convert the block address dba to the corresponding file number and block number.
Select dbms_utility.data_block_address_file (to_number ('14003b4)) file#, dbms_utility.data_block_address_block (to_number (' 14003b4)) block# from dual
FILE# BLOCK#
--
5 948
Dump the data block
Alter system dump datafile 5 block 948
Check the dump file to find the description section of the leaf block
Leaf block dump
=
Header address 532770404=0x1fc16e64
Kdxcolev 0
KDXCOLEV Flags =-
Kdxcolok 0
Kdxcoopc 0x80: opcode=0: iot flags=--- is converted=Y
Kdxconco 2
Kdxcosdc 0
Kdxconro 322
Kdxcofbo 680=0x2a8
Kdxcofeo 1502=0x5de
Kdxcoavs 822
Kdxlespl 0
Kdxlende 0
Kdxlenxt 20972469=0x14003b5
Kdxleprv 0=0x0
Kdxledsz 0
Kdxlebksz 8036
Row#0 [8017] flag: -, lock: 0, len=19
Col 0; len 9; (9): 31 36 44 34 30 37 31 38 33
Col 1; len 6; (6): 01 40 03 a5 000a
Row#1 [7997] flag: -, lock: 0, len=20
Col 0; len 10; (10): 31 38 30 35 30 30 30 34 31 45
Col 1; len 6; (6): 01 40 03 9b 000c
Kdxlenxt: the block address of the next leaf node
Kdxleprv: the block address of the previous leaf node
These two pointers are important for the SQL optimizer to perform index range scans operations on the index. Then comes the data content of the leaf block, which shows only two rows of records.
After reading the above, do you have any further understanding of how to understand dump data blocks? If you want to know more knowledge or related content, please follow the industry information channel, thank you for your support.
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.
Views: 0
*The comments in the above article only represent the author's personal views and do not represent the views and positions of this website. If you have more insights, please feel free to contribute and share.
Continue with the installation of the previous hadoop.First, install zookooper1. Decompress zookoope
"Every 5-10 years, there's a rare product, a really special, very unusual product that's the most un
© 2024 shulou.com SLNews company. All rights reserved.