In this post I’d like to show you how to dump a smallest logical structure of an Oracle Database – Data Block. Have you ever interested what’s actually stored in the data block and if it’s possible to get any information by “dumping” it? Sure you have. You can dump a single data block of the datafile. Let me show you how it’s done
First of all let’s create a new tablespace and a table:
[php]SQL> create tablespace my_tbs datafile ‘/u01/oracle/oradata/repdb/my_tbs.dbf’ size 100m;
Tablespace created.
SQL> create table my_tab tablespace my_tbs as select * from dba_objects;
Table created.[/php]
Now let’s get more information about this table from DBA_SEGMENTS view:
[php]SQL> select header_file, header_block, bytes, blocks, extents from dba_segments where segment_name=’MY_TAB’;
HEADER_FILE HEADER_BLOCK BYTES BLOCKS EXTENTS
———– ———— ———- ———- ———-
7 9 7340032 896 22
SQL> [/php]
From the output we can say that the header block of the table is stored in the datafile 7, block 9. The size of the table 7340032 bytes (7.3 Mb) and it contains 896 blocks and 22 extents. After getting block size of the database, let’s do some math:
[php]SQL> show parameter db_block_size
NAME TYPE VALUE
———————————— ———– ——————————
db_block_size integer 8192
SQL>
[/php]
BLOCKS (dba_segments) * DB_BLOCK_SIZE (parameter file) = BYTES (dba_segments)
896*8192=7340032
Now let’s query DBA_EXTENTS view to get some information on extents and data blocks of this table:
[php]SQL> col segment_name format a10
SQL> set pagesize 100
SQL> select segment_name, extent_id, block_id, blocks, bytes from dba_extents where segment_name=’MY_TAB’;
SEGMENT_NA EXTENT_ID BLOCK_ID BLOCKS BYTES
———- ———- ———- ———- ———-
MY_TAB 0 9 8 65536
MY_TAB 1 17 8 65536
MY_TAB 2 25 8 65536
MY_TAB 3 33 8 65536
MY_TAB 4 41 8 65536
MY_TAB 5 49 8 65536
MY_TAB 6 57 8 65536
MY_TAB 7 65 8 65536
MY_TAB 8 73 8 65536
MY_TAB 9 81 8 65536
MY_TAB 10 89 8 65536
MY_TAB 11 97 8 65536
MY_TAB 12 105 8 65536
MY_TAB 13 113 8 65536
MY_TAB 14 121 8 65536
MY_TAB 15 129 8 65536
MY_TAB 16 137 128 1048576
MY_TAB 17 265 128 1048576
MY_TAB 18 393 128 1048576
MY_TAB 19 521 128 1048576
MY_TAB 20 649 128 1048576
MY_TAB 21 777 128 1048576
[/php]
Using DBA_EXTENTS view we can get BLOCK_ID range and number of data blocks in each extent. Look at the following output:
[php]MY_TAB 0 9 8 65536
MY_TAB 1 17 8 65536[/php]
Here it’s seen that the first extent (0) contains 8 blocks which id range between 9 and 17
9 (block_id) + 8 (number of blocks) = 17 (block_id of the next extent)
Ok, 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
[php]SQL> select header_file, header_block from dba_segments where segment_name=’MY_TAB’;
HEADER_FILE HEADER_BLOCK
———– ————
7 9
SQL> [/php]
The dump file will be stored in UDUMP directory (user_dump_dest). We can use TRACEFILE_IDENTIFIER to specify part of the trace file name and find it quickly under the UDUMP directory.
[php]SQL> alter session set tracefile_identifier = db_block_dump;
Session altered.[/php]
Now, dump the data block 9 of the datafile 7:
[php]SQL> alter system dump datafile 7 block 9;
System altered.
SQL> [/php]
Ok, now open the second session (in new terminal) and open the trace file. You will see the following output:
[php]tail -f repdb_ora_18308_DB_BLOCK_DUMP.trc
*** 2011-10-28 17:05:21.015
Start dump data blocks tsn: 7 file#: 7 minblk 9 maxblk 9
buffer tsn: 7 rdba: 0x01c00009 (7/9)
scn: 0x0000.02eeaf02 seq: 0x01 flg: 0x04 tail: 0xaf021001
frmt: 0x02 chkval: 0x1a9f type: 0x10=DATA SEGMENT HEADER – UNLIMITED
Extent Control Header
—————————————————————–
Extent Header:: spare1: 0 spare2: 0 #extents: 22 #blocks: 895
last map 0x00000000 #maps: 0 offset: 4128
Highwater:: 0x01c0037a ext#: 21 blk#: 113 ext size: 128
#blocks in seg. hdr’s freelists: 0
#blocks below: 880
mapblk 0x00000000 offset: 21
Unlocked
Map Header:: next 0x00000000 #extents: 22 obj#: 130752 flag: 0x40000000
Extent Map
[/php]
Let’s try to read the above output :
* Start dump data blocks tsn: 7 file#: 7 minblk 9 maxblk 9
This means that the following lines is the output of datafile 7, block 9. Range of blocks can be given to dump:
[php]alter system dump datafile 1 block min 29081 block max 30539 [/php]
* 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. So here if we convert 0x01c00009 to decimal, we would get 7/9 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 (0x01c00009) to decimal and got – 29360137. Then pass this value to the above mentioned functions:
[php]SQL> select DBMS_UTILITY.DATA_BLOCK_ADDRESS_FILE(29360137) from dual;
DBMS_UTILITY.DATA_BLOCK_ADDRESS_FILE(29360137)
———————————————-
7
SQL> select DBMS_UTILITY.DATA_BLOCK_ADDRESS_BLOCK(29360137) from dual;
DBMS_UTILITY.DATA_BLOCK_ADDRESS_BLOCK(29360137)
———————————————–
9
SQL> [/php]
In order to convert hexadecimal value to the decimal online, you can use the following link:
http://www.statman.info/conversions/hexadecimal.html
By using DBMS_UTILITY.MAKE_DATA_BLOCK_ADDRESS function and passing file and block value, we can get RDA value:
[php]SQL> select DBMS_UTILITY.MAKE_DATA_BLOCK_ADDRESS(7,9) from dual;
DBMS_UTILITY.MAKE_DATA_BLOCK_ADDRESS(7,9)
—————————————–
29360137
SQL>
[/php]
* scn – is 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 0x0000.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:
[php]SQL> select current_scn from v$database;
CURRENT_SCN
———–
49249322
SQL> [/php]
* tail is 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 7 or Oracle 8 and higher block. 0x02 represents that it’s Oracle 8 and higher block. (0x01 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
* type : Type defines which type of block it is. In this example it is “Data Segment Header”
[php] Extent Header:: spare1: 0 spare2: 0 #extents: 22 #blocks: 895 [/php]
Then in the Extent Control Header part we can get information about extents. So it has 22 extents and 895 blocks.
[php] Highwater:: 0x01c0037a ext#: 21 blk#: 113 ext size: 128
#blocks in seg. hdr’s freelists: 0
#blocks below: 880 [/php]
Highwater mark is at extent 21, block 113. This extent has 128 blocks. And there’re 880 data block below it. Let’s do some math:
As it has 880 blocks below and the number of block is 895 (dump file didn’t count the header block), then there’re 895-880=15 data blocks free (and above the hightwater mark)
Moreover, as the highwater mark is at block 113 and the extent has 128 data blocks, then 128-113=15 data blocks are free
So this means that there’s 15*8192=122880 bytes (122Kb) free space
[php] Map Header:: next 0x00000000 #extents: 22 obj#: 130752 flag: 0x40000000[/php]
Next, we got object id. To find more details on that object, query sys.obj# view:
[php]SQL> col name format a10
SQL> select obj#, owner#, name, status, ctime, type# from sys.obj$ where obj#=130752;
OBJ# OWNER# NAME STATUS CTIME TYPE#
———- ———- ———- ———- ——— ———-
130752 0 MY_TAB 1 28-OCT-11 2
SQL> [/php]
So the owner id is 0 (which is SYS user) and type is 2 (which means table)
[php]SQL> select username from dba_users where user_id=0;
USERNAME
——————————
SYS
SQL> [/php]
and to get information about types, use the following query:
[php]select USERNAME,
count(decode(o.TYPE#, 2,o.OBJ#,”)) Tabs,
count(decode(o.TYPE#, 1,o.OBJ#,”)) Inds,
count(decode(o.TYPE#, 5,o.OBJ#,”)) Syns,
count(decode(o.TYPE#, 4,o.OBJ#,”)) Views,
count(decode(o.TYPE#, 6,o.OBJ#,”)) Seqs,
count(decode(o.TYPE#, 7,o.OBJ#,”)) Procs,
count(decode(o.TYPE#, 8,o.OBJ#,”)) Funcs,
count(decode(o.TYPE#, 9,o.OBJ#,”)) Pkgs,
count(decode(o.TYPE#,12,o.OBJ#,”)) Trigs,
count(decode(o.TYPE#,10,o.OBJ#,”)) Deps
from obj$ o,
dba_users u
where u.USER_ID = o.OWNER# (+)
group by USERNAME
order by USERNAME
[/php]
In order to show the free space, we can use DBMS_SPACE.UNUSED_SPACE procedure. Let’s create a procedure :
[php]SQL> create or replace
2 procedure show_space
3 ( p_segname in varchar2,
4 p_owner in varchar2 default user,
5 p_type in varchar2 default ‘TABLE’,
6 p_partition in varchar2 default NULL )
7 authid current_user
8 as
9 l_free_blks number;
10 l_total_blocks number;
11 l_total_bytes number;
12 l_unused_blocks number;
13 l_unused_bytes number;
14 l_LastUsedExtFileId number;
15 l_LastUsedExtBlockId number;
16 l_LAST_USED_BLOCK number;
17
18 procedure p( p_label in varchar2, p_num in number )
19 is
20 begin
21 dbms_output.put_line( rpad(p_label,40,’.’) ||
22 p_num );
23 end;
24
25 begin
26 dbms_space.unused_space
27 ( segment_owner => p_owner,
28 segment_name => p_segname,
29 segment_type => p_type,
30 partition_name => p_partition,
31 total_blocks => l_total_blocks,
32 total_bytes => l_total_bytes,
33 unused_blocks => l_unused_blocks,
34 unused_bytes => l_unused_bytes,
35 LAST_USED_EXTENT_FILE_ID => l_LastUsedExtFileId,
36 LAST_USED_EXTENT_BLOCK_ID => l_LastUsedExtBlockId,
37 LAST_USED_BLOCK => l_LAST_USED_BLOCK );
38
39 p( ‘Total Blocks’, l_total_blocks );
40 p( ‘Total Bytes’, l_total_bytes );
41 p( ‘Total MBytes’, trunc(l_total_bytes/1024/1024) );
42 p( ‘Unused Blocks’, l_unused_blocks );
43 p( ‘Unused Bytes’, l_unused_bytes );
44 p( ‘Last Used Ext FileId’, l_LastUsedExtFileId );
45 p( ‘Last Used Ext BlockId’, l_LastUsedExtBlockId );
46 p( ‘Last Used Block’, l_LAST_USED_BLOCK );
47 end;
48
49 /
Procedure created.
SQL> set serveroutput on
SQL> exec show_space(‘MY_TAB’,’SYS’);
Total Blocks……………………….896
Total Bytes………………………..7340032
Total MBytes……………………….7
Unused Blocks………………………15
Unused Bytes……………………….122880
Last Used Ext FileId………………..7
Last Used Ext BlockId……………….777
Last Used Block…………………….113
PL/SQL procedure successfully completed.
SQL> [/php]
Now I copy/paste the math I’ve done above again to compare both results.
As it has 880 blocks below and the number of block is 895 (dump file didn’t count the header block), then there’re 895-880=15 data blocks free (and above the hightwater mark)
Moreover, as the highwater mark is at block 113 and the extent has 128 data blocks, then 128-113=15 data blocks are free
So this means that there’s 15*8192=122880 bytes (122Kb) free space
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. In the next post I’ll try to go more deep and dump a data block that contains data, make some transactional changes on the data and see what happens in the background in the block level!
or due to the OS bugs. And in some cases we’re not able to find in which folder files were created. For that, you can use the simplest command in Linux to get the output for the folders with its size sorted. Here’s the command:
