Data block
A data block is the smallest unit of storage in an Oracle database. Every database has a default block size (specified when the database is created), although blocks in different tablespaces may have different block sizes.
An extent consist of one or more contiguous Oracle data blocks. A block determines the finest level of granularity of where data can be stored. One data block corresponds to a specific number of bytes of physical space on disk.
Information about data blocks can be retrieved from the data dictionary views USER_SEGMENTS and USER_EXTENTS. These views show how many blocks are allocated for database object and how many blocks are available(free) in a segment/extent.
Dumping data blocks
Start by getting the file and block number to dump. Example:
SQL> SELECT
2 dbms_rowid.rowid_relative_fno(rowid) REL_FNO,
3 dbms_rowid.rowid_block_number(rowid) BLOCKNO,
4 dbms_rowid.rowid_row_number(rowid) ROWNO,
5 empno, ename
6 FROM emp WHERE empno = 7369;
REL_FNO BLOCKNO ROWNO EMPNO ENAME
---------- ---------- ---------- ---------- ----------
4 20 0 7369 SMITH
Dump the block:
SQL> alter system dump datafile 4 block 20; System altered.
Look for the newly created dump file in your UDUMP directory.
Use the following syntax to dump multiple blocks:
ALTER SYSTEM dump datafile <file_id> block min <block_id> block max <block_id+blocks-1>;
Analyzing data block dumps
From the above block dump:
block_row_dump: tab 0, row 0, @0x1d49 tl: 38 fb: --H-FL-- lb: 0x0 cc: 8 col 0: [ 3] c2 4a 46 col 1: [ 5] 53 4d 49 54 48 col 2: [ 5] 43 4c 45 52 4b col 3: [ 3] c2 50 03 col 4: [ 7] 77 b4 0c 11 01 01 01 col 5: [ 2] c2 09 col 6: *NULL* col 7: [ 2] c1 15
Converting back to table values:
- Col 0 (EMPNO)
SQL> SELECT utl_raw.cast_to_number(replace('c2 4a 46',' ')) value FROM dual;
VALUE
----------
7369
- Col 2 (ENAME) - simply convert the hex values to ascii - 53 4d 49 54 48 -> SMITH. Alternatively:
SQL> SELECT utl_raw.cast_to_varchar2(replace('53 4d 49 54 48',' ')) value FROM dual;
VALUE
---------
SMITH
Also see
- Data block address (DBA)
- Extent, contiguous set of data blocks.
- PCTFREE, the percentage free block storage parameter.
