Data block

From Oracle FAQ

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  block min  block max ;

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

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/24387280/viewspace-696065/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/24387280/viewspace-696065/