Oracle数据块转储并查看块的详细内容

查看表所属的文件号和块号：

SYS AS SYSDBA@ORCL>SELECTsegment_name,file_id,block_id from dba_extents where owner='OE' ANDsegment_name like 'ORDERS%';

SEGMENT_NAME    FILE_ID  BLOCK_ID

ORDERS              5      1176

定位找出 user tracefile 的位置：

SYS AS SYSDBA@ORCL>SHOW PARAMETERuser_dump;

NAME                                 TYPE                 VALUE

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

user_dump_dest                       string /u01/app/oracle/diag/rdbms/orc

l/ORCL/trace

使用 dump 命令转储块信息到 trace 文件中，并找到相应的 trace 文件

SYS AS SYSDBA@ORCL>ALTER SYSTEM DUMPDATAFILE 5 BLOCK 1176;

System altered.

查看块转储后的文件路径：

方法一：

SYS AS SYSDBA@ORCL>select value fromv$diag_info where name='Default Trace File';

VALUE

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

/u01/app/oracle/diag/rdbms/orcl/ORCL/trace/ORCL_ora_2286.trc

方法二：

先查到系统进程号：

SYS AS SYSDBA@ORCL>select spid fromv$process

2   where addr=(select paddr fromv$session

3   where sid=(select distinct sidfrom v$mystat));

SPID

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

2286

再通过进程号查找磁盘文件：

[oracle@oracle trace]$ pwd

/u01/app/oracle/diag/rdbms/orcl/ORCL/trace

[oracle@oracle trace]$ ll *2286*

-rw-rw----. 1 oracle oracle 3611 Oct 1709:11 ORCL_ora_2286.trc

-rw-rw----. 1 oracle oracle   69 Oct 17 09:11 ORCL_ora_2286.trm

查看 trace 文件内容：

[oracle@oracletrace]$ cat ORCL_ora_2286.trm

@2|2|gBZ-T4s62"2286|ORCL|

M/gBZ-T4s62~81K2

6?i6X-T4s62~c9z

7?zmJ~H1L

[oracle@oracletrace]$ cat ORCL_ora_2286.trc

Trace file/u01/app/oracle/diag/rdbms/orcl/ORCL/trace/ORCL_ora_2286.trc

Oracle Database 11g Enterprise EditionRelease 11.2.0.1.0 - Production

With the Partitioning, OLAP, Data Miningand Real Application Testing options

ORACLE_HOME =/u01/app/oracle/product/11.2.2/db_1

System name:    Linux

Node name:      oracle

Release:        2.6.39-400.211.1.el6uek.i686

Version:        #1 SMP Fri Nov 15 13:38:12 PST 2013

Machine:        i686

Instance name: ORCL

Redo thread mounted by this instance: 1

Oracle process number: 29

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

*** 2018-10-17 09:11:22.135

*** SESSION ID:(125.45) 2018-10-1709:11:22.135

*** CLIENT ID:() 2018-10-17 09:11:22.135

*** SERVICE NAME:(SYS$USERS) 2018-10-1709:11:22.135

*** MODULE NAME:(sqlplus@oracle (TNSV1-V3)) 2018-10-17 09:11:22.135

*** ACTION NAME:() 2018-10-17 09:11:22.135

Start dump data blocks tsn: 6 file#:5minblk 1176 maxblk 1176

Block dump from cache:

Dump of buffer cache at level 4 for tsn=6,rdba=20972696

Block dump from disk:

buffer tsn: 6 rdba: 0x01400498 (5/1176)

scn: 0x0000.000b9726 seq: 0x01 flg: 0x04tail: 0x97262001

frmt: 0x02 chkval: 0xde2e type: 0x20=FIRSTLEVEL BITMAP BLOCK

Hex dump of block: st=0, typ_found=1

Dump of memory from 0xB6E10600 to0xB6E12600

B6E10600 0000A220 01400498 000B972604010000  [ .....@.&.......]

B6E10610 0000DE2E 00000000 0000000000000000  [................]

B6E10620 00000000 00000000 0000000000000000  [................]

Repeat 1 times

B6E10640 00000000 00000000 0000000000000004  [................]

B6E10650 FFFFFFFF 00000000 0000000300000008  [................]

B6E10660 00010001 00000000 0000000100000000  [................]

B6E10670 00000004 00000003 4A850B454A850B45  [........E..JE..J]

B6E10680 00000000 00000000 0000000000000000  [................]

B6E10690 01400499 00000000 0000000000000008  [..@.............]

B6E106A0 00000008 014004A0 0000000000000000  [......@.........]

B6E106B0 00000000 00000005 0000000000000001  [................]

B6E106C0 00011E7F 00000003 0000000001400498  [..............@.]

B6E106D0 00000008 00000000 0000000000000000  [................]

B6E106E0 00000000 00000000 0000000000000000  [................]

Repeat 9 times

B6E10780 00000000 00000000 0000000053551511  [..............US]

B6E10790 00000000 00000000 0000000000000000  [................]

Repeat 485 times

B6E125F0 00000000 00000000 0000000097262001  [............. &.]

Dump of First Level Bitmap Block

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

nbits : 4 nranges: 1        parent dba:  0x01400499   poffset: 0

unformatted: 0       total: 8         first useful block: 3

owning instance : 1

instance ownership changed at 08/14/2009 14:59:17

Last successful Search 08/14/2009 14:59:17

Freeness Status:  nf1 0      nf2 1     nf3 0      nf4 4

Extent Map Block Offset: 4294967295

First free datablock : 3

Bitmap block lock opcode 0

Locker xid:     :  0x0000.000.00000000

Inc #: 0 Objd: 73343

HWMFlag: HWM Set

Highwater::  0x014004a0  ext#: 0     blk#: 8      ext size: 8

#blocks in seg. hdr's freelists: 0

#blocks below: 5

mapblk  0x00000000  offset: 0

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

DBARanges :

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

0x01400498  Length: 8      Offset: 0

0:Metadata   1:Metadata   2:Metadata  3:75-100% free

4:75-100% free   5:75-100%free   6:75-100% free   7:25-50% free

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

End dump data blocks tsn: 6 file#: 5 minblk1176 maxblk 1176

tkprof 工具格式化 trace 文件：

[oracle@oracle trace]$ tkprof/u01/app/oracle/diag/rdbms/orcl/ORCL/trace/ORCL_ora_3998.trc /tmp/3998.trc

TKPROF: Release 11.2.0.1.0 - Development on 星期三 10 月 17 10:15:32 2018

Copyright (c) 1982, 2009, Oracle and/or itsaffiliates.  All rights reserved.

[oracle@oracle trace]$ cat /tmp/2286.trc

TKPROF: Release 11.2.0.1.0 - Development on 星期三 10 月 17 09:57:44 2018

Copyright (c) 1982, 2009, Oracle and/or itsaffiliates.  All rights reserved.

Trace file:/u01/app/oracle/diag/rdbms/orcl/ORCL/trace/ORCL_ora_2286.trc

Sort options: default

********************************************************************************

count   = number of times OCI procedure was executed

cpu     = cpu time in seconds executing

elapsed = elapsed time in seconds executing

disk    = number of physical reads of buffers from disk

query   = number of buffers gotten for consistent read

current = number of buffers gotten in current mode (usually for update)

rows    = number of rows processed by the fetch or execute call

********************************************************************************

Trace file:/u01/app/oracle/diag/rdbms/orcl/ORCL/trace/ORCL_ora_2286.trc

Trace file compatibility: 11.1.0.7

Sort options: default

1  session in tracefile.

0  user  SQL statements in trace file.

0  internal SQL statements intrace file.

0  SQL statements in trace file.

0  unique SQL statements in tracefile.

79  lines in trace file.

0        elapsed seconds in trace file.