ORACLE space management experiment 1: exploring the structure of the data file header under LMT tablespace management and the record directory (?) in the bitmap Area (?) [] The experiment is divided into two steps: 1. the local tablespace managed by LMT. The structure analysis of the data file during ASSM automatic segment management ORACLE11G: 0 operating system block, 1-2 is the file header, and 3-127 is the bitmap information. What is stored at and after the 128 mark
ORACLE space management experiment 1: exploring the structure of the data file header under LMT tablespace management and the record directory (?) in the bitmap Area (?) [] The experiment is divided into two steps: 1. the local table space managed by LMT. The structure analysis of the data file during ASSM automatic segment management is performed in the ORACLE 11G: 0 operating system block, 1-2 is the file header, and 3-is the bitmap information. What is stored at and after the 128 mark
ORACLE space management experiment 1: exploring the structure of data file headers under LMT tablespace management and record methods in the bitmap Area
Directory (?) [+]
The experiment is divided into two steps:
1. Local tablespace managed by LMT. Structure Analysis of data files during Automatic ASSM segment ManagementORACLE 11G: 0 operating system block, 1-2 is the file header, 3-is the bitmap information. Data is stored at the beginning and end of 128-it may be the data of the segment header or segment.
When ORACLE is 10 Gb, there are only 8 data file headers to store bitmap information. -- This article has not been tested.
2. Record of the usage of the partition in the bitmap block-block 3 is used in the first record area. This article describes Block 3.The bitmap block uses the binary value 1 to indicate the start number of a partition-or the position of the first allocable partition. The number of partitions in this record does not matter with the automatic or fixed partition size. You can create two tablespaces for verification by creating two different allocation methods.
In combination with the experiment, I understand that the bitmap represents the location of the partition, not the absolute location of DBA, but the relative location of the first few, when allocating, El finds the first available area based on the information in the bitmap block. -- I don't know if this is correct. If you cannot figure it out, let's look at the experiment later.
3. Which operations will release space? What do I do when a space is released?
Bitmap changes when a segment is deleted: If the segment is dropped or TRUNCATE, the corresponding partition is recycled. If only the DELETE data is deleted, the partition space will not be recycled-the space in the data block will not be recycled-high water level.
For example, table A of partition 1, 2, 3, 4, and 5 where TRUNCATE Zone 2 is located,
Truncate table a; DDL operation, perform a full checkpoint, and then DUMP the data file No. 3 in the record area in the bitmap. It is found that FIRST has changed and zone space is released.
It is the change in the line information of Block 3 of the DUMP data file: RelFno: 14, BeginBlock: 128, Flag: 0, First: 1, Free: 63487
4. Enable FlashbackDROP recycle bin FunctionVerify the differences in Oracle zone search
Method:
Enable the flash-back DROP recycle bin Function-11G is enabled by default,For example, if a data file contains multiple segments (tables), the table creation sequence is different, and the allocated areas are different in sequence in the data file. In this case, if you drop the First table created in the data file and DUMP the bitmap block, you will find that the value of First: 4 will change to First: 1, and then there may be a very long time before ffffffff0f00.However, it is said that when flash back is enabled, the search for a zone scans the entire bitmap. The value of First: 4 is useless. After drop, the table system command rename, And the partition and data still exist in the data file. If no zone is available for the entire bitmap area, the earliest drop zone will be released at the drop time.If the space of all the tables in the recycle bin is released, the data file will be extended.
If the recycle bin function of the flash-back DROP is not enabled,If you delete the First table created in the data file or TRUNCATE, the value of First: 4 in the bitmap will not change, and will be allocated directly from First: 4, down, it is not until the central part of the data file is allocated that it returns to the beginning to find it idle.
Note: The calculation method of the record used in the bitmap is as follows:
DUMP this block, you can see the following:
RelFno: 7, BeginBlock: 128, Flag: 0, First: 4, Free: 63451
0F00000000000000
This is because, in a block, hexadecimal is used to represent the binary system. We should convert the hexadecimal system into a binary system, and check the number of binary 1 to calculate the number of the starting zone.
A simpler calculation method is: Each hexadecimal represents a maximum of four 1 s, which are hexadecimal 1 -- binary 1, hexadecimal 3 -- binary 11, hexadecimal 7 -- binary 11, hexadecimal F -- binary 1111
Here, 0F is four binary 1, which indicates that four partitions are allocated.
Step 1 of the experiment: analyze the structure of data files in the tablespace locally managed by LMT and ASSM automatic segment Management
Create a new table space test4, create a new data file in the table space test4, insert a row of data, and make a full checkpoint. You can find the position of the segment header from dba_segments.header_block, blocks 1-4 and 127-131 of the DUMP test4 tablespace.
BYS @ bys3> create table test4 (aa int) tablespace test4;
Table created.
BYS @ bys3> insert into test4 values (99889 );
1 row created.
BYS @ bys3> commit;
Commit complete.
BYS @ bys3> select segment_name, header_block, header_file, blocks from dba_segments where segment_name = 'test4 ';
SEGMENT_NAME HEADER_BLOCK HEADER_FILE BLOCKS
------------------------------------------------
TEST4 130 14 8
BYS @ bys3> alter system checkpoint; --- make a full check point. Otherwise, the newly inserted data is not written into the data file.
System altered.
#############
BYS @ bys3> alter system dump datafile 14 block min 1 block max 4;
System altered.
BYS @ bys3> select value from v $ diag_info where name like 'de % ';
VALUE
Bytes ----------------------------------------------------------------------------------------------------
/U01/diag/rdbms/bys3/bys3/trace/bys3_ora_12335.trc
BYS @ bys3> alter system dump datafile 14 block min 127 block max 131;
System altered.
BYS @ bys3> select value from v $ diag_info where name like 'de % ';
VALUE
Bytes ----------------------------------------------------------------------------------------------------
/U01/diag/rdbms/bys3/bys3/trace/bys3_ora_12377.trc
################
DUMP data block content analysis
Part 2: -- Bitmap header
Start dump data blocks tsn: 9 file #: 14 minblk 1 maxblk 4
Block 1 (file header) not dumped: use dump file header command -- DUMP data file 1st blocks -- Block header, use alter system set events 'immediate trace name file_hdrs level 3 ';
Block dump from cache: -- this is from the buffer cache.
Dump of buffer cache at level 4 for tsn = 9 rdba = 58720258
BH (0x217f7538) file #: 14 rdba: 0x03800002 (14/2) class: 13 ba: 0x2171c000 -- For details about BH, see: detailed description of Buffer Header-DUMP buffer combined with various fields in the X $ BH View
Set: 3 pool: 3 bsz: 8192 bsi: 0 sflg: 1 pwc: 0, 0
Dbwrid: 0 obj:-1 objn: 1 tsn: 9 afn: 14 hint: f
Hash: [0x2a000041c, 0x2a000041c] lru: [0x22fed538, 0x22fe44d8]
Ckptq: [NULL] fileq: [NULL] objq: [0x24444154, 0x22fe44f0] objaq: [0x2444414c, 0x22fe44f8]
St: XCURRENT md: NULL fpin: 'kt fbwh0d: KT fbsearch' tch: 2
Flags: block_written_once redo_since_read
LRBA: [0x0. 0.0] LSCN: [0x0.0] HSCN: [0xffff. ffffffff] HSUB: [1]
Block dump from disk:
Buffer tsn: 9 rdba: 0x03800002 (14/2) ------ tsn: 9 tablespace number, rdba: 0x03800002 (14/2) 14 file No. 2
Scn: 0x0000. 0077 dfdb seq: 0x02 flg: 0x04 tail: 0xdfdb1d02
Frmt: 0x02 chkval: 0xb96b type: 0x1d = KTFB Bitmapped File Space Header -- the information of this block is a bitmap block.
Hex dump of block: st = 0, typ_found = 1
Dump of memory from 0xB6C22600 to 0xB6C24600
B6C22600 running a30d 03800002 0077 DFDB 04020000 [......]
B6C22610 116b96b 0000000E 00000008 00000500 [k ......]
B6C22620 00000001 00000000 00000000 2017007e [......]
B6C22630 000004FF 00000001 0000008F 00000003 [......]
B6C22640 00000000 00000000 00000000 00000000 [......]
B6C22650 00000080 00000008 00000000 00000000 [......]
B6C22660 00000000 00000000 00000000 00000000 [......]
Repeat 504 times
B6C245F0 00000000 00000000 00000000 DFDB1D02 [......]
File Space Header Block:
Header Control:
RelFno: 14, Unit: 8, Size: 1280, Flag: 1 --- relative file number 14, Unit: 8 -- Allocation Unit. The Size of each block is 8192 ,. Size: 1280 -- Size of the data file-1280*8 K = 10 M
AutoExtend: NO, Increment: 0, MaxSize: 0 ---
Initial Area: 126, Tail: 1279, First: 1, Free: 143 --- Initial Area: 126, which indicates that there are 126 blocks used to record bitmap information -, from 3 to 127-11g, there are 6
Deallocation scn: 3.0
Header Opcode:
Save: No Pending Op
Block dump from cache:
Dump of buffer cache at level 4 for tsn = 9 rdba = 58720259 -- TNS9 is the tablespace number and rdba = 58720259 is the FILE and BLOCK. The RDBA calculation method is as follows:
BYS @ bys3> select dbms_utility.make_data_block_address (14,2) from dual;
DBMS_UTILITY.MAKE_DATA_BLOCK_ADDRESS (14,2)
------------------------------------------
58720258
#######################
Part 3-the first bitmap block-area allocation can be observed here. For details, see the second step of the experiment ############
Block dump from cache:
Dump of buffer cache at level 4 for tsn = 9 rdba = 58720260
Block dump from disk:
Buffer tsn: 9 rdba: 0x03800004 (14/4) -- bitmap Block No. 4, not used.
Scn: 0x0000. 0077dc17 seq: 0x01 flg: 0x04 tail: 0xdc171e01
Frmt: 0x02 chkval: 0x8364 type: 0x1e = KTFB Bitmapped File Space Bitmap
Hex dump of block: st = 0, typ_found = 1
Dump of memory from 0xB6C22600 to 0xB6C24600
B6C22600 running a21e 03800004 0077DC17 04010000 [......]
B6C245F0 00000000 00000000 00000000 DC171E01 [......]
File Space Bitmap Block:
BitMap Control:
RelFno: 14, BeginBlock: 508032, Flag: 0, First: 0, Free: 63488 -- no partition information is recorded in this bitmap .. When the data file is used at the beginning, it is only divided into a partition and cannot be recorded using this bitmap block.
######################################## ######################################## ###################
Start dump data blocks tsn: 9 file #: 14 minblk 127 maxblk 131
Block dump from cache:
Dump of buffer cache at level 4 for tsn = 9 rdba = 58720383
Block dump from disk:
Buffer tsn: 9 rdba: 0x0380007f (14/127)
Scn: 0x0000. 0077dd0d seq: 0x01 flg: 0x04 tail: 0xdd0d1e01
Frmt: 0x02 chkval: 0x40d9 type: 0x1e = KTFB Bitmapped File Space Bitmap -- this block is still a Bitmap block of the partition.
Hex dump of block: st = 0, typ_found = 1
Dump of memory from 0xB6BE1600 to 0xB6BE3600
Repeat 507 times
B6BE35F0 00000000 00000000 00000000 DD0D1E01 [......]
File Space Bitmap Block:
BitMap Control:
RelFno: 14, BeginBlock: 62980224, Flag: 0, First: 0, Free: 63488
-- No partition information is recorded in this bitmap block .. When the data file is used at the beginning, it is only divided into a partition and cannot be recorded using this bitmap block.
######################################## ########################################
Block dump from cache:
Dump of buffer cache at level 4 for tsn = 9 rdba = 58720384
BH (0x21be7f70) file #: 14 rdba: 0x03800080 (14/128) class: 8 ba: 0x218e0000
Set: 3 pool: 3 bsz: 8192 bsi: 0 sflg: 1 pwc: 0, 0
Dbwrid: 0 obj: 22963 objn: 22963 tsn: 9 afn: 14 hint: f
Hash: [0x2b3fd83c, 0x2b3fd83c] lru: [0x21fe8ec0, 0x227f1d68]
Ckptq: [NULL] fileq: [NULL] objq: [0x24440ce4, 0x21fe8ed8] objaq: [0x21fe8ee0, 0x21ff6f2c]
St: XCURRENT md: NULL fpin: 'ktspfwh6: ktspffbmb' tch: 1
Flags: block_written_once redo_since_read
LRBA: [0x0. 0.0] LSCN: [0x0.0] HSCN: [0xffff. ffffffff] HSUB: [1]
Block dump from disk:
Buffer tsn: 9 rdba: 0x03800080 (14/128)
Scn: 0x0000. 0077dfe3 seq: 0x02 flg: 0x04 tail: 0xdfe32002
Frmt: 0x02 chkval: 0x9faf type: 0x20 = FIRST LEVEL BITMAP BLOCK
--- We can see that the first block is the L1 block of the newly created table, followed by L2 and segment header-L3. It is not the topic of this article. I did not post it..
Hex dump of block: st = 0, typ_found = 1
Step 2: record the partition usage in the bitmap Block
1. Normal analysis: BH (0x22fe4424) file #: 14 rdba: 0x03800003 (14/3) class: 12 ba: 0x22c56000
Set: 3 pool: 3 bsz: 8192 bsi: 0 sflg: 1 pwc: 0, 0
Dbwrid: 0 obj:-1 objn: 1 tsn: 9 afn: 14 hint: f
Hash: [0x2a7fb618, 0x2a7fb618] lru: [0x217f75ec, 0x20bfca24]
Ckptq: [NULL] fileq: [NULL] objq: [0x217f7604, 0x24444154] objaq: [0x217f760c, 0x2444414c]
St: XCURRENT md: NULL fpin: 'kt fbwh36: ktfbbsearch' tch: 2
Flags: block_written_once redo_since_read
LRBA: [0x0. 0.0] LSCN: [0x0.0] HSCN: [0xffff. ffffffff] HSUB: [1]
Block dump from disk:
Buffer tsn: 9 rdba: 0x03800003 (14/3)
Scn: 0x0000. 0077 dfdb seq: 0x01 flg: 0x04 tail: 0xdfdb1e01
Frmt: 0x02 chkval: 0x4c9b type: 0x1e = KTFB Bitmapped File Space Bitmap -- 3rd blocks, start to use Bitmap to record usage in this data File
Hex dump of block: st = 0, typ_found = 1
Dump of memory from 0xB6C22600 to 0xB6C24600
B6C22600 running a21e 03800003 0077 DFDB 04010000 [......]
B6C22610 20174c9b 2017000e 00000080 00000000 [. L ......]
B6C22620 00000001 running f7ff 00000000 00000000 [......]
B6C22630 00000000 00000000 00000001 00000000 [......]
B6C22640 00000000 00000000 00000000 00000000 [......]
Repeat 506 times
B6C245F0 00000000 00000000 00000000 DFDB1E01 [......]
File Space Bitmap Block:
BitMap Control:
RelFno: 14, BeginBlock: 128, Flag: 0, First: 1, Free: 63487 -- because it is a newly created tablespace, and a new table is created in this tablespace, only one partition is allocated for the moment.
0100000000000000 0000000000000000 0000000000000000 0000000000000000
Here01Indicates the number of blocks in the partition. The hexadecimal value 01 indicates that the binary value is 1, and the binary value 1 indicates that a partition is allocated, because a row of data is inserted in the new table, so only one zone is allocated .. You can run the manual partition allocation command and then DUMP, alter table test2 allocate extent;
If it is First: 3, it indicates that three zones have been allocated, then the corresponding hexadecimal format is0700, binary 111 .. See the figure below:
2. There are two tables in the data file. Delete the first one.--- Changes from Bitmap
First: 1,BYS @ bys3> create table test4 (aa int) tablespace test2;
Table created.
BYS @ bys3> insert into test4 select object_id from dba_objects;
17556 rows created.
BYS @ bys3> commit;
Commit complete.
BYS @ bys3> alter system checkpoint;
System altered.
BYS @ bys3> alter system dump datafile 7 block 3;
System altered.
BYS @ bys3> select value from v $ diag_info where name like 'de % ';
VALUE
Bytes ----------------------------------------------------------------------------------------------------
/U01/diag/rdbms/bys3/bys3/trace/bys3_ora_22652.trc
In this case, the bitmap record of Block 3 in the DUMP file is as follows:
File Space Bitmap Block:
BitMap Control:
RelFno: 7, BeginBlock: 128, Flag: 0
, "First: 1204,Free: 62284
FFFFFFFFfffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff
Ffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff
Ffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff
Ffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff
Fffffffffffffffffffffffffffffffffffff
FFFFFFF0F000000000000000000
Delete the first table:
BYS @ bys3> truncate table test2;
Table truncated.
BYS @ bys3> alter system checkpoint;
System altered.
BYS @ bys3> exit
New session:
BYS @ bys3> alter system dump datafile 7 block 3;
System altered.
BYS @ bys3> select value from v $ diag_info where name like 'de % ';
VALUE
Bytes ----------------------------------------------------------------------------------------------------
/U01/diag/rdbms/bys3/bys3/trace/bys3_ora_22707.trc
In this case, the bitmap record of Block 3 in the DUMP file is as follows:
RelFno: 7, BeginBlock: 128, Flag: 0,
First: 1,Free: 63451
0100000000000000 0000000000000000 0000000000000000 0000000000000000
0000000000000000 0000000000000000 0000000000000000 0000000000000000
0000000000000000 0000000000000000 0000000000000000 0000000000000000
0000000000000000 0000000000000000 0000000000000000 0000000000000000
0000000000000000 0000000000000000
0FFFFFFFF0F000000000000000000
3. The Partition Distribution Method of different sizes does not affect the record in the bitmap area. BYS @ bys3> create tablespace test3 datafile '/u01/oradata/bys3/test3.dbf' size 100 m uniform size 8 m;
Tablespace created.
BYS @ bys3> create table test33 (aa int) tablespace test3;
Table created.
BYS @ bys3> insert into test33 values (11 );
1 row created.
BYS @ bys3> commit;
Commit complete.
BYS @ bys3> alter system checkpoint;
System altered.
BYS @ bys3> alter system dump datafile 8 block 3;
System altered.
BYS @ bys3> select * from v $ DIAG_INFO;
/U01/diag/rdbms/bys3/bys3/trace/bys3_ora_23058.trc
###########
RelFno: 8, BeginBlock: 128, Flag: 0, First: 1, Free: 63487
0100000000000000 0000000000000000 0000000000000000 0000000000000000
####### 33
BYS @ bys3> alter table test33 allocate extent;
Table altered.
BYS @ bys3> alter system checkpoint;
System altered.
BYS @ bys3> alter system dump datafile 8 block 3;
############ 3
RelFno: 8, BeginBlock: 128, Flag: 0, First: 2, Free: 63486
0300000000000000 -- here, The hexadecimal value 3 is converted to binary value 11, indicating that two partitions are allocated.
