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

Preface

A segment is any entity that consumes physical storage space in a database (a segment may exist in multiple data files because physical data files

Is the basic physical storage unit that makes up the logical tablespace)

Recently, there are some questions about the HEADER_BLOCK of the learning section (segment) and interval (extent). This paper records the experimental process of the exploration and the relevant summary, if there is anything wrong, please point out. Take the SCOTT.EMP table as an example (the following test environment is Oracle Database 10g Release 10.2.0.5.0-64bit Production):

SELECT FILE_ID, BLOCK_ID, BLOCKS FROM DBA_EXTENTS WHERE OWNER ='& OWNER' AND SEGMENT_NAME ='& TABLE_NAME'

SELECT HEADER_FILE, HEADER_BLOCK, BYTES, BLOCKS, EXTENTS FROM DBA_SEGMENTS WHERE OWNER='&OWNER' AND SEGMENT_NAME='&SEGMENT_NAME'

As shown above, the HEADER_BLOCK in DBA_SEGMENTS is different from the BLOCK_ID of DBA_EXTENTS (HEADER_BLOCK: the 27th block of the file ID is 4, and the BLOCK_ID of the first block of the interval is the 25th block) for the following reasons:

The first block of the first area of a segment is FIRST LEVEL BITMAP BLOCK, the second block is SECOND LEVEL BITMAP BLOCK, these two blocks are used to manage free block, the third block is PAGETABLE SEGMENT HEADER, this block is the HEADER_BLOCK in segment, and the later block is used to record data. So 25, 2, 27. For details, please refer to Chapter 5 of the book "step-by-step ORCLE: database Management, Optimization, and backup."

Let's create a table and test whether it is the same rule, as shown below:

SQL > CREATE TABLE TEST1.MMM 2 AS 3 SELECT * FROM DBA_OBJECTS; Table created. SQL > COL SEGMENT_NAME FOR A32 ORDER BY BLOCK_ID ASC > SELECT SEGMENT_NAME 2, FILE_ID 3, BLOCK_ID 4, BLOCKS 5 FROM DBA_EXTENTS 6 WHERE SEGMENT_NAME='MMM' AND OWNER='TEST1' 7 ORDER BY BLOCK_ID ASC SEGMENT_NAME FILE_ID BLOCK_ID BLOCKS---MMM 76 9 8MMM 76 17 8MMM 76 25 8MMM 76 33 8MMM 76 41 8MMM 76 49 8MMM 76 57 8MMM 76 65 8MMM 76 73 8MMM 76 81 8MMM 76 89 8 SEGMENT_NAME FILE_ID BLOCK_ID BLOCKS -MMM 76 97 8MMM 76 105 8MMM 76 113 8MMM 76 121 8MMM 76 129 8MMM 76 137 128MMM 76 265 128MMM 76 393 128MMM 76 521 128MMM 76 649 128MMM 76 777 128 22 rows selected. SQL > SELECT HEADER_FILE 2, HEADER_BLOCK 3, BYTES 4, BLOCKS 5, EXTENTS 6 FROM DBA_SEGMENTS 7 WHERE OWNER='TEST1' AND SEGMENT_NAME='MMM'; HEADER_FILE HEADER_BLOCK BYTES BLOCKS EXTENTS- 76 11 7340032 896 22

As shown above, the header_block of the segment object TEST1.MMM is 11, and the first block object ID of the corresponding interval is 9, which is also 9 / 2 / 11, so let's take a look at the DUMP data block, as shown below

SQL > alter system dump datafile 76 block 9; System altered. SQL > alter system dump datafile 76 block 10; System altered. SQL > alter system dump datafile 76 block 11; System altered. SQL > select user_dump.value 2 | |'/'3 | | lower (instance.value) 4 | |'_ ora_' 5 | | v$process.spid 6 | | nvl2 (v$process.traceid,'_'7 | | v$process.traceid Null) 8 | |. Trc' "trace file" 9 from v$parameter user_dump 10 cross join v$parameter instance 11 cross join v$process 12 join v$session 13 on v$process.addr = v$session.paddr 14 where user_dump.name = 'user_dump_dest' 15 and instance.name =' instance_name' 16 and v$session.audsid = sys_context ('userenv',' sessionid') Trace file----/u01/app/oracle/admin/SCM2/udump/scm2_ora_22642.trc

The first block (block_id=9) of the first area is FIRST LEVEL BITMAP BLOCK, and the second block (block_id=10) is SECOND LEVEL BITMAP BLOCK. These two blocks are used to manage free block, and the third block (block_id=11) is PAGETABLE SEGMENT HEADER. This block is the HEADER_BLOCK in segment, and the latter block is used to record data.

However, there is a strange phenomenon. When we dump the data blocks of SCOTT.EMP, we find that the type of 25,26,27 data blocks are all represented by trans data,0x06 and Block Type is Table/cluster/index segment data block. I don't know if it's because the SCOTT.EMP object is under the USERS tablespace. However, USER tablespaces are also managed by ASSM. The details are not clear?

SQL > SELECT TABLESPACE_NAME 2, SEGMENT_SPACE_MANAGEMENT 3, ALLOCATION_TYPE 4, EXTENT_MANAGEMENT 5 FROM DBA_TABLESPACES 6 WHERE TABLESPACE_NAME='USERS'; TABLESPACE_NAME SEGMEN ALLOCATIO EXTENT_MAN---USERS AUTO SYSTEM LOCAL

So are all HEADER_BLOCK the third block in the segment? Does it have anything to do with the way the segment space is managed? Let's explore with the following experiment: create a tablespace for manual segment space management (Manual Segment Space Management).

SQL > CREATE TABLESPACE TBS_TEST_DATA 2 DATAFILE'/ u03 EXTENT MANAGEMENT LOCAL AUTOALLOCATE EXTENT MANAGEMENT LOCAL AUTOALLOCATE 5 SEGMENT SPACE MANAGEMENT MANUAL ONLINE; Tablespace created. SQL > create user test identified by test123456 2 default tablespace tbs_test_data; User created. SQL > grant connect, resource to test; Grant succeeded. SQL > CREATE TABLE TEST.KKK 2 AS 3 SELECT * FROM DBA_OBJECTS; Table created. SQL > COL SEGMENT_NAME FOR A32 ORDER BY BLOCK_ID ASC > SELECT SEGMENT_NAME 2, FILE_ID 3, BLOCK_ID 4, BLOCKS 5 FROM DBA_EXTENTS 6 WHERE SEGMENT_NAME='KKK' AND OWNER='TEST' 7 ORDER BY BLOCK_ID ASC SEGMENT_NAME FILE_ID BLOCK_ID BLOCKS---KKK 39 427785 128KKK 43 435249 8KKK 43 435257 8KKK 43 435265 8KKK 43 435273 8KKK 43 435281 8KKK 43 435289 8KKK 43 435297 8KKK 43 435305 8KKK 43 435313 8KKK 43 435321 8 SEGMENT_NAME FILE_ID BLOCK_ID BLOCKS -- KKK 43 435329 8KKK 48 436745 8KKK 48 436753 8KKK 48 436761 8KKK 48 436769 8KKK 48 436777 8KKK 48 436873 128KKK 40 444297 128KKK 43 447241 128KKK 52 449545 128KKK 2 458249 128 22 rows selected. SQL > SELECT HEADER_FILE 2, HEADER_BLOCK 3, BYTES 4, BLOCKS 5, EXTENTS 6 FROM DBA_SEGMENTS 7 WHERE OWNER='TEST' AND SEGMENT_NAME='KKK'; HEADER_FILE HEADER_BLOCK BYTES BLOCKS EXTENTS- 43 435249 7340032 896 22 SQL >

SQL > alter system dump datafile 43 block 435249; System altered. SQL > select user_dump.value 2 | |'/'3 | | lower (instance.value) 4 | |'_ ora_' 5 | | v$process.spid 6 | | nvl2 (v$process.traceid,'_'7 | | v$process.traceid Null) 8 | |. Trc' "trace file" 9 from v$parameter user_dump 10 cross join v$parameter instance 11 cross join v$process 12 join v$session 13 on v$process.addr = v$session.paddr 14 where user_dump.name = 'user_dump_dest' 15 and instance.name =' instance_name' 16 and v$session.audsid = sys_context ('userenv',' sessionid') Trace file----/u01/app/oracle/admin/SCM2/udump/scm2_ora_27792.trc

As shown below, the block type is DATA SEGEMENT HEADER-UNLIMITED, and rdba: (the block address of segment header is) is 0x0ac6a431. In fact, this is the first block (not in terms of block_id size), because of manual segment space management, this technique is implemented by assigning a free list (freelist) in the Segment Header to manage the use of Block. To put it simply, you can think of a free list as a linked list in a data structure. ORACLE uses a series of algorithms to add or remove Block to the free list (freelist) to achieve segment management.

Segment Header is the first extent header block (the first block) of a Segment. In Segment managed by FLM, header block is always the first block of segment. As shown below, in Extent Map, the address of the first interval is 0x0ac6a432, which is exactly 1 different from the block address 0x0ac6a431 of segment header, which means that the subsequent allocation is the block address next to segment header. Therefore, in the table space of manual segment space management (Manual Segment Space Management), the interval allocation order cannot be seen in the order of block_id size. This means that the block of FILE_ID=39 BLOCK_ID=427785 is not the first block of the first interval. This is what I have been struggling with for a long time in the experiment.

Summary

The above is the whole content of this article, I hope that the content of this article has a certain reference and learning value for your study or work, if you have any questions, you can leave a message and exchange, 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.