Tuesday, August 26, 2008

How to dump Database block and how to read it

I did this small test on my 11g instance. I created a small table EMP with 3 rows and then I dumped the block.

Create table, Insert 3 rows and commit:

Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options


SQL> create table emp(empno number(5), ename varchar2(30)) tablespace users;

Table created.

SQL> insert into emp values(7369,'SCOTT');

1 row created.

SQL> insert into emp values(7812,'SMITH');

1 row created.

SQL> insert into emp values(9241,'TOM');

1 row created.

SQL> commit;

Commit complete.

Get the block-number for rows:

select rowid,empno,ename,
dbms_rowid.rowid_relative_fno(rowid) fileno,
dbms_rowid.rowid_block_number(rowid) blockno,
dbms_rowid.rowid_row_number(rowid) rowno, rownum
from emp;


ROWID EMPNO ENAME FILENO BLOCKNO ROWNO ROWNUM
------------------ ---------- ------------------------------ ---------- ---------- ---------- ----------
AAARJGAAEAAAABEAAA 7369 SCOTT 4 68 0 1
AAARJGAAEAAAABEAAB 7812 SMITH 4 68 1 2
AAARJGAAEAAAABEAAC 9241 TOM 4 68 2 3


Dump the block:

SQL> alter system dump datafile 4 block 68;

System altered.

Trace file can be found in User Dump Dest.

SQL> show parameter user

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
license_max_users integer 0
parallel_adaptive_multi_user boolean TRUE
redo_transport_user string
user_dump_dest string /dmecsoid/oracle/diag/rdbms/de
v11g/dev11g/trace
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
oracle@dmecsoid->cd /dmecsoid/oracle/diag/rdbms/dev11g/dev11g/trace
oracle@dmecsoid->

oracle@dmecsoid->ls -lrt dev11g_ora_10038.trc
-rw-r----- 1 oracle dba 37429 Aug 25 23:26 dev11g_ora_10038.trc
oracle@dmecsoid->

How to read Block Data:

block_row_dump:
tab 0, row 0, @0x1f8b
tl: 13 fb: --H-FL-- lb: 0x1 cc: 2
col 0: [ 3] c2 4a 46 --- 4a (hex to decimal)=74-1=73, 46(hex to decimal)=70-1=69-> 73 69(empno)
col 1: [ 5] 53 43 4f 54 54--- 53=S, 43=C, 4f=O, 54=T, 54=T -> S C O T T (ename)
tab 0, row 1, @0x1f7e
tl: 13 fb: --H-FL-- lb: 0x1 cc: 2
col 0: [ 3] c2 4f 0d --- 4f (hex to decimal)=79-1=78, 0d(hex to decimal)=13-1=12 -> 78 12(empno)
col 1: [ 5] 53 4d 49 54 48--- 53=S, 4d=M, 49=I, 54=T, 48=H -> S M I T H (ename)
tab 0, row 2, @0x1f73
tl: 11 fb: --H-FL-- lb: 0x1 cc: 2
col 0: [ 3] c2 5d 2a --- 5d (hex to decimal)=93-1=92, 2a(hex to decimal)=42-1=41 -> 92 41 (empno)
col 1: [ 3] 54 4f 4d --- 54=T, 4f=O, 4d=M -> T O M (ename)
end_of_block_dump

No comments: