oracle 数据块解析,Oracle 数据块(data block)的构造和解析

Oracle 数据块(data block)的结构和解析

First of all let’s create a new tablespace and a table:

SQL> create tablespace my_tbs datafile '/u01/oradata/chenlin/my_tbs.dbf' size 200m ;

Tablespace created.

then:

SQL> create table my_tab tablespace my_tbs as select * from dba_objects  ;

Table created.

Now let’s get more information about this table from DBA_SEGMENTS view:

SQL> select header_file, header_block, bytes, blocks, extents from dba_segments where segment_name='MY_TAB';

HEADER_FILE HEADER_BLOCK BYTES BLOCKS EXTENTS

----------- ------------ -------- ---------- ----------

12 17 4194304 1024 19

From the output we can say that the header block of the table is stored in the datafile 12, block 17.

The size of the table 4194304 bytes (4.2 Mb) and it contains 1024 blocks and 19 extents.

After getting block size of the database, let’s do some math:

SQL> show parameter db_block_size

NAME TYPE VALUE

-------------- ----------- --------------------------

db_block_size integer 4096

BLOCKS (dba_segments) * DB_BLOCK_SIZE (parameter file) = BYTES (dba_segments)

so : 1024 * 4096 =  4194304

Now let’s query DBA_EXTENTS view to get some information on extents and data blocks of this table:

SQL> col segment_name format a20

SQL> select segment_name, extent_id, block_id,  blocks, bytes from dba_extents where segment_name='MY_TAB';

SEGMENT_NAME EXTENT_ID BLOCK_ID BLOCKS BYTES

-------------------- ---------- ---------- ---------- -------

MY_TAB 0 17 16 65536

MY_TAB 1 33 16 65536

MY_TAB 2 49 16 65536

MY_TAB 3 65 16 65536

MY_TAB 4 81 16 65536

MY_TAB 5 97 16 65536

MY_TAB 6 113 16 65536

MY_TAB 7 129 16 65536

MY_TAB 8 145 16 65536

MY_TAB 9 161 16 65536

MY_TAB 10 177 16 65536

MY_TAB 11 193 16 65536

.............................

19 rows selected.

we do this test by follow  info :

SEGMENT_NAME EXTENT_ID BLOCK_ID BLOCKS BYTES

-------------------- --------- --------- ---------- ----------

MY_TAB 0 17 16 65536

MY_TAB 1 33 16 65536

Here we see that the first extent (0) contains 16 blocks which id range between 17 and 33 ,

so: 17 (block_id) + 16 (number of blocks) = 33 (block_id of the next extent)

And now let’s start dumping the header block of the table. For this we need data file and header block number.

It can be taken from the DBA_SEGMENTS

SQL> select header_file, header_block from dba_segments where segment_name='MY_TAB';

HEADER_FILE HEADER_BLOCK

----------- ------------

12 17

See more about dba_segments:

http://docs.oracle.com/cd/B19306_01/server.102/b14237/statviews_4097.htm#i1626854

Now, dump the data block 12 of the datafile 17:

notice :you should rm  your udump file   by force  first  , and  relogin  again

SQL> alter system dump datafile 12 block 17;

System altered.

If you use  SecureCRT  , you can use the command  : rz  or sz to download or upload files

Clone a new session and change to oracle user  by using : su - oracle

[oracle@chance udump]$ ll

total 4

-rw-r-----  1 oracle oinstall 2106 May  6 22:15 chenlin_ora_7831.trc

[oracle@chance udump]$sz chenlin_ora_7831.trc

rz

Starting zmodem transfer.  Press Ctrl+C to cancel.

Transferring chenlin_ora_7831.trc...

100%       2 KB    2 KB/s 00:00:01       0 Errors

Let's find  the file chenlin_ora_7831.trc  at  C:\Users\Administrator\Downloads  and see the infomation  in it :

#---------------------------------begin--------------------------

/u01/admin/chenlin/udump/chenlin_ora_7831.trc

Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production

With the Partitioning, OLAP and Oracle Data Mining options

JServer Release 9.2.0.4.0 - Production

ORACLE_HOME = /u01/oracle

System name:Linux

Node name:chance

Release:2.6.9-89.EL

Version:#1 Mon Jun 22 12:19:40 EDT 2009

Machine:i686

Instance name: chenlin

Redo thread mounted by this instance: 1

Oracle process number: 14

Unix process pid: 7831, image: oracle@chance (TNS V1-V3)

*** SESSION ID:(11.17) 2012-05-06 22:15:42.270

Start dump data blocks tsn: 14 file#: 12 minblk 17 maxblk 17

buffer tsn: 14 rdba: 0x03000011 (12/17)

scn: 0x0000.000a40da seq: 0x01 flg: 0x04 tail: 0x40da1001

frmt: 0x02 chkval: 0x28a8 type: 0x10=DATA SEGMENT HEADER - UNLIMITED

Extent Control Header

-----------------------------

Extent Header:: spare1: 0      spare2: 0      #extents: 19     #blocks: 1023

last map  0x00000000  #maps: 0      offset: 2080

Highwater::  0x03000351  ext#: 18     blk#: 64     ext size: 256

#blocks in seg. hdr's freelists: 0

#blocks below: 831

mapblk  0x00000000  offset: 18

Unlocked

Map Header:: next  0x00000000  #extents: 19   obj#: 30558  flag: 0x40000000

Extent Map

-----------------------------

0x03000012  length: 15

0x03000021  length: 16

0x03000031  length: 16

0x03000041  length: 16

0x03000051  length: 16

0x03000061  length: 16

0x030000c1  length: 16

0x030000d1  length: 16

0x030000e1  length: 16

0x030000f1  length: 16

0x03000101  length: 16

0x03000111  length: 256

0x03000211  length: 256

0x03000311  length: 256

nfl = 1, nfb = 1 typ = 1 nxf = 0 ccnt = 0

SEG LST:: flg: UNUSED lhd: 0x00000000 ltl: 0x00000000

End dump data blocks tsn: 14 file#: 12 minblk 17 maxblk 17

#---------------------------------------end------------------------------------------

* Start dump data blocks tsn: 14 file#: 12 minblk 17 maxblk 17

This means that the following lines is the output of datafile 17, block 17. Range of blocks can be given to dump:

SQL> alter system dump datafile 1 block min 1 block max 17

* rdba: 0x03000011

rdba is the Data Block Address which is internal representation of the block address.The first 10 bits contains file number, and the rest 22 bits contains block id.

if we want to convert 0x03000011 to decimal, we would get 12/17 which is shown between brackets.

Moreover, DBMS_UTILITY.DATA_BLOCK_ADDRESS_FILE and DBMS_UTILITY.DATA_BLOCK_ADDRESS_BLOCK can be used to get the file and block number based on RDA value. But before using these functions, rda should be converted to the decimal value.

So here, we convert the rda value (0x03000011) to decimal and got  50331665.

In order to convert hexadecimal value to the decimal online, you can use the following link:http://www.statman.info/conversions/hexadecimal.html

SQL>  select DBMS_UTILITY.DATA_BLOCK_ADDRESS_FILE(50331665) as fileNo , DBMS_UTILITY.DATA_BLOCK_ADDRESS_BLOCK(50331665) as blockNo from dual;

FILENO BLOCKNO

---------- ----------

12 17

By using DBMS_UTILITY.MAKE_DATA_BLOCK_ADDRESS function and passing file and block value, we can get RDA value:

SQL> select DBMS_UTILITY.MAKE_DATA_BLOCK_ADDRESS(12,17) from dual;

DBMS_UTILITY.MAKE_DATA_BLOCK_ADDRESS(12,17)

-------------------------------------------

50331665

* scn: 0x0000.000a40dais the SCN value of the block which defines when it was last modified. We can compare it with each transaction SCN value inside the dump file. The SCN value of the data block is 0×0000.02eeaf02. If second part of this value (02eeaf02) is converted to the decimal, then we got – 49196802. If we query the current_scn value we got:

The SCN value of the data block is 0×0000.02eeaf02.

If second part of this value (000a40da) is converted to the decimal, then we got – 671962. If we query the current_scn value we got:

SQL> select dbms_flashback.get_system_change_number from dual;

GET_SYSTEM_CHANGE_NUMBER

------------------------

683658

* tailis used to keep track of consistency information between the beginning and end of the block against the possibility of distribution of oracle blocks over multiple OS blocks

* frmt – is the block format which tells whether it’s Oracle 9 or Oracle 10 and higher block. 0×02 represents that it’s Oracle 8 and higher block. (0×01 represents that it’s Oracle 7 block)

* chkval– which is checksum written to the blocks when it is set and used by Oracle in part to check the consistency and validity of the block, and  you show set  the parameter db_block_checksum  to true in init.ora file

* type : Type defines which type of block it is. In this example it is “Data Segment Header”

* Extent Header: spare1: 0    spare2: 0      #extents: 19     #blocks: 1023

Then in the Extent Control Header part we can get information about extents. So it has 19 extents and 1023 blocks.

* Highwater:  0x03000351  ext#: 18     blk#: 64     ext size: 256

#blocks in seg. hdr's freelists: 0

#blocks below: 831

Highwater mark  ( 50332497 ) is at extent 18, block 64. This extent has 256 blocks. And there’re 831 data block below it. Let’s do some math:

As it has 831 blocks below and the number of block is 1023 (dump file didn’t count the header block), then there’re 1023-831=192 data blocks free (and above the hightwater mark).

Moreover, as the highwater mark is at block 64  and the extent has  256  data blocks, then 256-64 = 192 data blocks are free,

So this means that there’s 192*4096 = 786432 bytes (768Kb) free space left

* Map Header:next  0x00000000  #extents: 19   obj#: 30558  flag: 0x40000000

Next, we got object id. To find more details on that object, query sys.obj# view:

SQL>  set lines 180

SQL> select obj#, owner#, name, status, ctime, type# from sys.obj$ where obj#=30558;

OBJ# OWNER# NAME STATUS CTIME TYPE#

------- ------- ------- ---------- --------- ----

30558 0 MY_TAB 1 06-MAY-12 2

SQL> select username from dba_users where user_id=0;

USERNAME

------------------------------

SYS

So we can see the owner id is 0 (SYS user) and type is 2 (it means table)

In order to show the free space, we can use DBMS_SPACE.UNUSED_SPACE procedure. Let’s create  procedure :

SQL> create or replace procedure show_space

( p_segname in varchar2,

p_owner in varchar2 default user,

p_type in varchar2 default 'TABLE',

p_partition in varchar2 default NULL )

authid current_user

as

l_free_blks number;

l_total_blocks number;

l_total_bytes number;

l_unused_blocks number;

l_unused_bytes number;

l_LastUsedExtFileId number;

l_LastUsedExtBlockId number;

l_LAST_USED_BLOCK number;

procedure p( p_label in varchar2, p_num in number )

is

begin

dbms_output.put_line( rpad(p_label,40,'.') ||

p_num );

end;

begin

dbms_space.unused_space

( segment_owner => p_owner,

segment_name => p_segname,

segment_type => p_type,

partition_name => p_partition,

total_blocks => l_total_blocks,

total_bytes => l_total_bytes,

unused_blocks => l_unused_blocks,

unused_bytes => l_unused_bytes,

LAST_USED_EXTENT_FILE_ID => l_LastUsedExtFileId,

LAST_USED_EXTENT_BLOCK_ID => l_LastUsedExtBlockId,

LAST_USED_BLOCK => l_LAST_USED_BLOCK );

p( 'Total Blocks', l_total_blocks );

p( 'Total Bytes', l_total_bytes );

p( 'Total MBytes', trunc(l_total_bytes/1024/1024) );

p( 'Unused Blocks', l_unused_blocks );

p( 'Unused Bytes', l_unused_bytes );

p( 'Last Used Ext FileId', l_LastUsedExtFileId );

p( 'Last Used Ext BlockId', l_LastUsedExtBlockId );

p( 'Last Used Block', l_LAST_USED_BLOCK );

end show_space;

/

SQL> set serveroutput on

SQL> exec show_space('MY_TAB','SYS');

Total Blocks............................1024

Total Bytes.............................4194304

Total MBytes............................4

Unused Blocks...........................192

Unused Bytes............................786432

Last Used Ext FileId....................12

Last Used Ext BlockId...................785

Last Used Block.........................64

PL/SQL procedure successfully completed.

As it has 831 blocks below and the number of block is 1023 (dump file didn’t count the header block), then there’re 1023-831=192 data blocks free (and above the hightwater mark)

Moreover, as the highwater mark is at block 64  and the extent has  256  data blocks, then 256-64 = 192 data blocks are free,

So this means that there’s 192*4096 = 786432 bytes (768Kb) free space left .

As you see comparison of both values led us to the same results .

So in this post you’ve learned how to dump a header block of the segment and how to read it.

ps: 如果你想真正知道oracle的block块里面真正有些什么，请耐心读完这篇文章～