Oracle Truncate table principle: Analysis of ASSM three-level bitmap

Assm level-3 bitmap is the basic skill knowledge of dba. Here we use the test method to verify the principle of level-3 bitmap.

1. Create tables required for the lab environment

SQL> create tablespace a1 datafile '/oracle/app/oracle/oradata/ora11/a1.dbf' size 20 m;
Tablespace created.
SQL> create user a1 identified by a1 default tablespace a1;
User created.
SQL> grant dba to a1;
Grant succeeded.
SQL> connect a1/a1
Connected.
SQL> create table a as select * from dba_tables;
Table created.

After the experiment environment is created, we need to understand the bitmap and data block distribution of this section A. We do not directly look up from the view, here we consider that we need to use bbed to obtain the bitmap and data block distribution from data blocks.

2. What L2 bitmap blocks are pointed to by the segment header block/L3 bitmap block?

Which segments are composed? We need to obtain this information from the header block. After you create a segment, even if you do not insert any data into it, the system will pre-allocate some areas to you. So the segment header block is the block, which can be queried through dba_segments. Even if the segment is truncate, we can still query the segment header information from dba_segments.

SQL> select HEADER_FILE, HEADER_BLOCK from dba_segments where SEGMENT_NAME = 'a' and owner = 'A1 ';
HEADER_FILE HEADER_BLOCK
-----------------------
5 130

After finding the field header block, you can use bbed to mine information.

BBED> set filename '/oracle/app/oracle/oradata/ora11/a1.dbf'
FILENAME/oracle/app/oracle/oradata/ora11/a1.dbf
BBED> set block 130
BLOCK #130.
BBED> dump/v offset 0 count 20
File:/oracle/app/oracle/oradata/ora11/a1.dbf (0)
Block: 130 Offsets: 0 to 19 Dba: 0x00000000
-------------------------------------------------------
23a20000 82004001 d8321400 00000104 l #.@.....
848d0000 l ....
L ....
Here we can see that the first offset of the segment header block is 23. The L2 bitmap block pointed to by the segment header is at the offset 5192 position. Remember that the field header block is marked as 23.

BBED> dump/v offset 5192 count 100
File:/oracle/app/oracle/oradata/ora11/a1.dbf (0)
Block: 130 Offsets: 5192 to 5291 Dba: 0x00000000
-------------------------------------------------------
81004001 00000000 00000000 l ..@.............
00000000 00000000 00000000 l ................
00000000 00000000 00000000 l ................
00000000 00000000 00000000 l ................
00000000 00000000 00000000 l ................
00000000 00000000 00000000 l ................
00000000 l ....

From here we can find our L2 bitmap block, 81004001. Here there is only one L2 block, because it is followed by 00000000 (null), because it involves the issue of the operating system's bytecode, here, we need to change the value to 01400081. After conversion, we can use the following query to find the file number and block number.

SQL> select transform (to_number ('123456', 'xxxxxxxx') as fileno, dbms_utility.data_block_address_block (to_number ('123456', 'xxxxxxxx') as blockno from dual;

FILENO BLOCKNO

--------------------
5
5 129

3. What L1 bitmap blocks do L2 bitmap block point?

Next, we can continue to read our L2 bitmap block to find our L1 bitmap block. The first offset of L2 is 21. Remember that the L2 bitmap block is marked as 21.

BBED> set block 129
BLOCK #129.

BBED> dump/v offset 0 count 20
File:/oracle/app/oracle/oradata/ora11/a1.dbf (0)
Block: 129 Offsets: 0 to 19 Dba: 0x00000000
-------------------------------------------------------
21a20000 81004001 d4321400 00000604 l !. @.....
48a10000 l H

L2 points to L1 data blocks starting from offset 116. When it ends, check whether there is 00000000 (null)

BBED> dump/v offset 116 count 100
File:/oracle/app/oracle/oradata/ora11/a1.dbf (0)
Block: 129 Offsets: 116 to 215 Dba: 0x00000000
-------------------------------------------------------
80004001 01000100 90004001 l ..@.......@.....
A0004001 01000100 b0004001 01000100 l ........
C0004001 01000100 d0004001 01000100 l ........
E0004001 05000100 00000000 00000000 l ............
00000000 00000000 00000000 l ................
00000000 00000000 00000000 l ................
00000000 l ....

From the dump, we can see that there is an address in front, followed by 01000100, which is relatively regular. After about 7 groups, it will become 00000000 (empty. Similar to the above, we need to convert 80004001 to 01400080 because of the byte order problem. Then we can obtain the block location through the following query. In the latter part of 01000100, the former 01 fills up all the blocks under the L1, and there is no empty data block. The latter 01 indicates that the block is generated by instance 1. The last 05000100,05 represents an empty block under the L1 and can be inserted. In the later 01, we said that it is an instance. If the system is a rac system and Node 2 also inserts data, 05000200 is displayed here.

SQL> select partition (to_number ('123456', 'xxxxxxxx') as fileno, dbms_utility.data_block_address_block (to_number ('123456', 'xxxxxxxx') as blockno from dual
Union
Select partition (to_number ('123456', 'xxxxxxxx') as fileno, dbms_utility.data_block_address_block (to_number ('123456', 'xxxxxxxx') as blockno from dual
Union
Select transform (to_number ('014000a0 ', 'xxxxxxxx') as fileno, dbms_utility.data_block_address_block (to_number ('014000a0', 'xxxxxxxx') as blockno from dual
Union
Select transform (to_number ('014000b0', 'xxxxxxxx') as fileno, dbms_utility.data_block_address_block (to_number ('014000b0', 'xxxxxx') as blockno from dual
Union
Select transform (to_number ('014000c0 ', 'xxxxxxxx') as fileno, dbms_utility.data_block_address_block (to_number ('014000c0', 'xxxxxxxx') as blockno from dual
Union
Select transform (to_number ('014000d0', 'xxxxxxxx') as fileno, dbms_utility.data_block_address_block (to_number ('014000d0', 'xxxxxx') as blockno from dual
Union
Select transform (to_number ('014000e0', 'xxxxxxxx') as fileno, dbms_utility.data_block_address_block (to_number ('014000e0', 'xxxxxx') as blockno from dual;
FILENO BLOCKNO
--------------------
5 128
5 144
5 160
5 176
5 192
5 208
5 224

7 rows selected.

Based on the above query, we found information about 7 L1 blocks.
4. Which data blocks are directed by L1 bitmap blocks?
We found the L1 block pointed to on our L2 block, and clearly knew which L1 is full and which L1 has idle blocks. We will analyze the last L1 bitmap block with idle blocks.

BBED> set block 224
BLOCK #224.

BBED> dump/v offset 0 count 10
File:/oracle/app/oracle/oradata/ora11/a1.dbf (0)
Block: 224 Offsets: 0 to 9 Dba: 0x00000000
-------------------------------------------------------
20a20000 e0004001 d432 l .@.
<16 bytes per line>

The first offset of L1 is 20. Remember that the ID of the L1 bitmap block is 20.
L1 points to the position of the data block starting from offset 204. When it ends, check whether there is 00000000 (null)

BBED> dump/v offset 204 count 80
File:/oracle/app/oracle/oradata/ora11/a1.dbf (0)
Block: 224 Offsets: 204 to 283 Dba: 0x00000000
-------------------------------------------------------
E0004001 08000000 00000000 e8004001 l ...... 08000000 08000000 00000000 00000000 l ................
00000000 00000000 00000000 l ................
00000000 00000000 00000000 l ................
00000000 00000000 00000000 l ................

We can see that the values here are e0004001, 08000000,00000000, the next group is e8004001, 08000000,08000000, and the surface is 00000000 (empty ).
The two indicate that L1 points to the starting position of the data block. For example, e0004001 indicates File 5, block 224, that is, it itself. 08000000 indicates that all seven consecutive blocks after this block are. While e8004001, 08000000,08000000 is file 5. Block 232,08000000 represents the same number of consecutive 7 blocks after this block. The last 08000000 represents offset, which we can ignore here. So here we can know that the specific data blocks under our L1 block are:
224 (it itself), 225,226,227,228,229,230,231,232,233,234,235,236,237,238,239. Use the following statement to verify the situation.

SQL> select distinct dbms_rowid.rowid_block_number (rowid) from a order by 1

We can see that 223 is followed directly by 225, and this is skipped directly. This is because our 224 is a L1 bitmap block, followed by the 225,226,227,228,229,230,231,232,233 we just mentioned. But the problem is, we cannot see the 234 to 239 below here? This is because 234 to 239 are still idle and unformatted blocks, but it has been locked by L1. So can L1 see these situations? We can observe the offset 396.

BBED> dump/v offset 396 count 50
File:/oracle/app/oracle/oradata/ora11/a1.dbf (0)
Block: 224 Offsets: 396 to 445 Dba: 0x00000000
-------------------------------------------------------
11111111 11000000 00000000 l ................
00000000 00000000 00000000 l ................
00000000 00000000 00000000 l ................
0000 l ..

Here we can see 11111111,111000000. So what does this mean? If this block is full, it is 1. If it is unformatted, it is 0, which is exactly the same as what we saw above.