Oracle中DUMP转储方法 收藏

大家在学习研究ORACLE的过程中，为了更深入的了解内部的机制，免不了要进程DUMP来研究学习，
下面我把学习过程中不断整理的各个DUMP罗列出来，希望大家指明错误和进行补充，共同进步！

—————————————————————————————

1、DUMP出控制文件
alter system (session) set events 'immediate trace name controlflevel 10'

—————————————————————————————

2、DUMP出日志文件分析
alter system dump logfile logfilename;

—————————————————————————————

3、DUMP出日志文件头分析
alter session set events 'immediate trace name REDOHDR level10'

———————————————————————————

4、DUMP出LIBRARY CACHE
alter system (session) set events 'immediate trace namelibrary_cache level 10'

—————————————————————————————

5、 DUMP出share_pool
alter system (session) set events 'immediate trace name heapdumplevel 10'

———————————————————————————

6、DUMP出所有数据文件头
alter system (session) set events 'immediate trace name file_hdrslevel 10';

———————————————————————————

7、DUMP出数据文件（只能是alter system）

alter system dump datafile n block m
如
select file_id,block_id,blocks from dba_extents wheresegment_name='EMPLOYEES'
FILE_ID BLOCK_ID BLOCKS
---------- ---------- ----------
5 81 8

SQL>; alter system dump datafile 5 block 81;

System altered.
另外是做很具体的针对某行所在的BLOCK 并DUMP出来研究，可以用如下方法

1、取BLOCK号
select dbms_rowid.rowid_block_number(rowid) from wdjk1999;

DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)
78

2、取文件号
select file_id from dba_data_files wheretablespace_name='WDJK1999'

FILE_ID

----------
16

SQL>; alter system dump datafile 16 block 78;

System altered.

—————————————————————————

8、DUMP出索引
SQL> select object_id from dba_objects whereobject_name=upper('index_name');
　　
OBJECT_ID
----------
70591
SQL> ALTER SESSION SET EVENTS 'immediate trace nameTREEDUMP level 70591';

—————————————————————————————

9、DUMP出回滚段信息
select xidusn,xidslot,xidsqn,ubafil,ubablk,ubasqn,ubarec fromv$transaction;

XIDUSN XIDSLOT XIDSQN UBAFIL UBABLK UBASQN UBAREC
---------- ---------- ---------- ---------- ---------- --------------------
1 31 442 2 21 242 52
dump对应的undo block
SYS@ning>altersystem dump datafile 2 block 21;
System altered.

——————————————————————————

10、 DUMP出系统状态分析 (只能是SYSTEM)
alter system set events 'immediate trace name systemdate level10'

——————————————————————————

11、 DUMP出分析进程状态分析
alter session set events 'immediate trace name PROCESSSTATE level10'

本文来自CSDN博客，转载请标明出处：http://blog.csdn.net/liweiah/archive/2009/07/16/4354563.aspx

 

其他（转载）

 

一.Memory Dumps

1).Global Area

ALTER SESSION SET EVENTS 'immediate trace nameglobal_area level n';

1 包含PGA
2 包含SGA
4 包含UGA
8 包含indrect memory

2).Library Cache

ALTER SESSION SET EVENTS 'immediate trace namelibrary_cache level n';

1 library cache统计信息
2 包含hash table histogram
3 包含object handle
4 包含object结构（Heap 0）

3).Row Cache

ALTER SESSION SET EVENTS 'immediate trace namerow_cache level n';

1 row cache统计信息
2 包含hash table histogram
8 包含object结构

4).Buffers

ALTER SESSION SET EVENTS 'immediate trace namebuffers level n';

1 buffer header
2 level 1 + block header
3 level 2 + block contents
4 level 1 + hash chain
5 level 2 + hash chain
6 level 3 + hash chain
8 level 4 + users/waiters
9 level 5 + users/waiters
10 level 6 + users/waiters

5).Buffer

ALTER SESSION SET EVENTS 'immediate trace namebuffer level n';

n为某个指定block的rdba，该命令可以转储某个block在buffer中的所有版本。

6).Heap

ALTER SESSION SET EVENTS 'immediate trace nameheapdump level level';

1 PGA摘要
2 SGA摘要
4 UGA摘要
8 Current call(CGA)摘要
16 User call(CGA)摘要
32 Large call(LGA)摘要
1025 PGA内容
2050 SGA内容
4100 UGA内容
8200 Current call内容
16400 User call内容
32800 Large call内容

7).Sub Heap

Oracle 9.0.1版本之前

ALTER SESSION SET EVENTS 'immediate trace nameheapdump_addr level n';

若n为subheap的地址，转储的是subheap的摘要信息
若n为subheap的地址+1，转储的则是subheap的内容

Oracle 9.2.0版本之后

ALTER SESSION SET EVENTS 'immediate trace nameheapdump_addr level n, addr m';

其中m为subheap的地址

n为1转储subheap的摘要，n为2转储subheap的内容

8).Process State

ALTER SESSION SET EVENTS 'immediate trace nameprocessstate level n';

9).System State

ALTER SESSION SET EVENTS 'immediate trace namesystemstate level n';

10).Error State

ALTER SESSION SET EVENTS 'immediate trace nameerrorstack level n';

0 Error stack
1 level 0 + function call stack
2 level 1 + process state
3 level 2 + context area

11).Hang Analysis

ALTER SESSION SET EVENTS 'immediate trace namehanganalyze level n';

12).Work Area

ALTER SESSION SET EVENTS 'immediate trace nameworkareatab_dump level n';

1 SGA信息
2 Workarea Table摘要信息
3 Workarea Table详细信息

13).Latches

ALTER SESSION SET EVENTS 'immediate trace namelatches level n';

1 latch信息
2 统计信息

14).Events

ALTER SESSION SET EVENTS 'immediate trace nameevents level n';

1 session
2 process
3 system

15).Locks

ALTER SESSION SET EVENTS 'immediate trace namelocks level n';

16).Shared ServerProcess

ALTER SESSION SET EVENTS 'immediate trace nameshared_server_state level n';

n取值为1~14

17).BackgroundMessages

ALTER SESSION SET EVENTS 'immediate trace namebg_messages level n';

n为pid+1

二.File Dumps

1).Block

Oracle 7之前

ALTER SESSION SET EVENTS 'immediate trace nameblockdump level n';

n为block的rdba

Oracle8以后

ALTER SYSTEM DUMP DATAFILE file# BLOCKblock#;

ALTER SYSTEM DUMP DATAFILE file#
BLOCK MIN minimum_block#
BLOCK MAX maximum_block#;

2).Tree Dump

ALTER SESSION SET EVENTS 'immediate trace nametreedump level n';

n为object_id

3).Undo SegmentHeader

ALTER SYSTEM DUMP UNDO_HEADER'segment_name';

4).Undo for aTransaction

ALTER SYSTEM DUMP UNDO BLOCK 'segment_name' XIDxidusn xidslot xidsqn;

5).File Header

ALTER SESSION SET EVENTS 'immediate trace namefile_hdrs level n';

1 控制文件中的文件头信息
2 level 1 + 文件头信息
3 level 2 + 数据文件头信息
10 level 3

6).Control file

ALTER SESSION SET EVENTS 'immediate trace namecontrolf level n';

1 文件头信息
2 level 1 + 数据库信息 + 检查点信息
3 level 2 + 可重用节信息
10 level 3

7).Redo log Header

ALTER SESSION SET EVENTS 'immediate trace nameredohdr level n';

1 控制文件中的redo log信息
2 level 1 + 文件头信息
3 level 2 + 日志文件头信息
10 level 3

8).Redo log

ALTER SYSTEM DUMP LOGFILE 'FileName';

ALTER SYSTEM DUMP LOGFILE 'FileName'
SCN MIN MinimumSCN
SCN MAX MaximumSCN
TIME MIN MinimumTime
TIME MAX MaximumTime
LAYER Layer
OPCODE Opcode
DBA MIN FileNumber . BlockNumber
DBA MAX FileNumber . BlockNumber
RBA MIN LogFileSequenceNumber . BlockNumber
RBA MAX LogFileSequenceNumber . BlockNumber;

其中time = (((((yyyy - 1988)) * 12 + mm - 1) * 31 +dd - 1) * 24 + hh) * 60 + mi) * 60 +ss;

9).Loghist

ALTER SESSION SET EVENTS 'immediate trace nameloghist level n';

1 dump控制文件中最早和最迟的日志历史项
>1 dump 2^n个日志历史项