在Oracle中，如何dump数据库内部结构？

事件ID

事件

说明

例子

10046

enable SQL statement timing

level 0：禁用SQL_TRACE，等价于SQL_TRACE=FALSE

level 1：启用标准的SQL_TRACE功能跟踪SQL语句，包括解析、执行、提取、提交、回滚、响应时间、服务时间、处理的行数，物理读和写的数目、执行计划以及其它一些额外信息，等价于SQL_TRACE=TRUE

level 4：Level 1 + 包括绑定变量（Bind Values）的详细信息

level 8：Level 1 + 包括等待事件

level 12：包括绑定变量与等待事件，包含Level 1 + Level 4 + Level 8

ALTER SESSION SET EVENTS '10046 trace name context  forever,level 12';

10053

CBO Enable optimizer trace

在分析SQL语句时，Dump出优化器所做的选择，级别level 1最详细

ALTER SESSION SET EVENTS '10053 trace name context forever, level 1';

10704

Print out information about what enqueues are being obtained

当启用的时候，可以查看锁的调用信息

ALTER SESSION SET EVENTS '10704 trace name context forever,level 10';

10710

Event 10710 - Trace Bitmap Index Access

跟踪位图索引的访问情况

ALTER SESSION SET EVENTS '10710 trace name context forever, level 1';

10711

Event 10711 - Trace Bitmap Index Merge Operation

跟踪位图索引合并操作

ALTER SESSION SET EVENTS '10711 trace name context forever, level 1';

10712

Event 10712 - Trace Bitmap Index OR Operation

跟踪位图索引或操作情况

ALTER SESSION SET EVENTS '10712 trace name context forever, level 1';

10713

Event 10713 - Trace Bitmap Index AND Operation

跟踪位图索引与操作

ALTER SESSION SET EVENTS '10713 trace name context forever, level 1';

10714

Event 10714 - Trace Bitmap Index MINUS Operation

跟踪位图索引MINUS操作

ALTER SESSION SET EVENTS '10714 trace name context forever, level 1';

10715

Event 10715 - Trace Bitmap Index Conversion to ROWIDs Operation

跟踪位图索引转换ROWID操作

ALTER SESSION SET EVENTS '10715 trace name context forever, level 1';

10716

Event 10716 - Trace Bitmap Index Compress/Decompress

跟踪位图索引压缩和解压缩情况

ALTER SESSION SET EVENTS '10716 trace name context forever, level 1';

10719

Event 10719 - Trace Bitmap Index DML

跟踪位图索引列的DML操作（引起位图索引改变的DML操作）

ALTER SESSION SET EVENTS '10719 trace name context forever, level 1';

10730

Event 10730 - Trace Fine Grained Access Predicates

跟踪细粒度审计的断语

ALTER SESSION SET EVENTS '10730 trace name context forever, level 1';

10731

Event 10731 - Trace CURSOR Statements

跟踪CURSOR的语句情况，跟踪游标声明，LEVEL 1表示跟踪父游标和子游标；LEVEL 2只跟踪子游标

ALTER SESSION SET EVENTS '10731 trace name context forever, level level';

10928

Event 10928 - Trace PL/SQL Execution

跟踪PL/SQL执行情况

ALTER SESSION SET EVENTS '10928 trace name context forever, level 1';

10938

Event 10938 - Dump PL/SQL Execution Statistics

转储PL/SQL执行统计信息，使用前需要执行脚本文件：$ORACLE_HOME/rdbms/admin/tracetab.sql

ALTER SESSION SET EVENTS '10938 trace name context forever, level 1';

flush_cache

刷新BUFFER CACHE

ALTER SESSION SET EVENTS 'immediate trace name flush_cache';

DROP_SEGMENTS

手工删除临时段。当这些临时段无法自动清除的时候可以手工清除

alter session set events 'immediate trace name DROP_SEGMENTS level ts#+1'; --ts#是指要删除临时段的表空间的ts#

global_area

1 包含PGA

2 包含SGA

4 包含UGA

8 包含indrect memory

ALTER SESSION SET EVENTS 'immediate trace name global_area level n';

Library Cache

1 library cache统计信息

2 包含hash table histogram

3 包含object handle

4 包含object结构（Heap 0）

ALTER SESSION SET EVENTS 'immediate trace name library_cache level 10';

Row Cache

dump数据字典缓冲区中的信息：

1 row cache统计信息

2 包含hash table histogram

8 包含object结构

ALTER SESSION SET EVENTS 'immediate trace name row_cache level n';

Process State

分析进程状态

ALTER SESSION SET EVENTS 'immediate trace name processstate level 10';

systemstate

dump所有系统状态和进程状态，分析系统状态，最好每10分钟一次，做三次对比

ALTER SESSION SET EVENTS 'immediate trace name systemstate level 10';

errorstack

dump错误栈信息，通常Oracle发生错误时前台进程将得到一条错误信息，但某些情况下得不到错误信息，可以采用这种方式得到Oracle错误

0 Error stack

1 level 0 + function call stack

2 level 1 + process state

3 level 2 + context area

alter session set events '604 trace name errorstack forever'; --表示当出现604错误时，dump错误栈和进程栈

ALTER SESSION SET EVENTS 'immediate trace name errorstack level n';

alter session set events '942 trace name errorstack level 3';

alter system set events='1438 trace name errorstack forever,level 3';

alter system set events='1438 trace name errorstack off';

Work Area

1 SGA信息

2 Workarea Table摘要信息

3 Workarea Table详细信息

ALTER SESSION SET EVENTS 'immediate trace name workareatab_dump level n';

Latches

1 latch信息

2 统计信息

ALTER SESSION SET EVENTS 'immediate trace name latches level n';

events

1 session

2 process

3 system

ALTER SESSION SET EVENTS 'immediate trace name events level n';

block

分析数据文件块，转储数据文件n的块m:

alter system dump datafile n block m;

ALTER SYSTEM DUMP DATAFILE file# BLOCK block#;

ALTER SYSTEM DUMP DATAFILE file# BLOCK MIN min # BLOCK MAX max #;

File Header

dump所有数据文件的头部信息：

1 控制文件中的文件头信息

2 level 1 + 文件头信息

3 level 2 + 数据文件头信息

10 level 3

alter session set events 'immediate trace name file_hdrs level 1'; --表示dump 所有数据文件头部的控制文件项。

alter session set events 'immediate trace name file_hdrs level 2'; --表示dump 所有数据文件的通用文件头。

alter session set events 'immediate trace name file_hdrs level 10'; --表示dump 所有数据文件的完整文件头。

Control file

转储控制文件：

1 文件头信息

2 level 1 + 数据库信息 + 检查点信息

3 level 2 + 可重用节信息

10 level 3

12

alter system set events 'immediate trace name controlf level 12';

Redo log Header

分析日志文件头：

1 控制文件中的redo log信息

2 level 1 + 文件头信息

3 level 2 + 日志文件头信息

10 level 3

alter session set events 'immediate trace name redohdr level 1'; --表示dump redo日志头部的控制文件项。

alter session set events 'immediate trace name redohdr level 2'; --表示dump redo日志的通用文件头。

alter session set events 'immediate trace name redohdr level 10'; --表示dump redo日志的完整文件头。

Redo log

分析日志文件

ALTER SYSTEM DUMP LOGFILE 'FileName';

Loghist

dump控制文件中的日志历史项：

1 dump控制文件中最早和最迟的日志历史项

>1 dump 2的n次方个日志历史项

alter session set events 'immediate trace name loghist level 1'; --表示只dump最早和最迟的日志历史项。

alter session set events 'immediate trace name loghist level 4'; --表示dump 16个日志历史项。

10013

Instance Recovery

用于监视事务恢复，在STARTUP时跟踪事务恢复

ALTER SESSION SET EVENTS '10013 trace name context forever, level 1';

10015

Undo Segment Recovery

转储UNDO SEGMENT头部，Dump Undo Segment Headers，在事务恢复后做Dump回退段头信息

ALTER SESSION SET EVENTS '10015 trace name context forever, level 1';

10032

sort statistics (SOR*)

转储排序的统计信息，level 10是最详细的

ALTER SESSION SET EVENTS '10032 trace name context forever, level 10';

10033

sort run information (SRD*/SRS*)

转储排序增长的统计信息

ALTER SESSION SET EVENTS '10033 trace name context forever, level 10';

10035

Write parse failures to alert log file

解析失败写入告警日志

alter system set events '10035 trace name context forever,leve 5';

alter system set events '10035 trace name context off';

10059

simulate error in logfile create/clear

模拟Redo日志中的创建和清除错误

10061

disable SMON from cleaning temp segment

阻止SMON进程在启动时清除临时段

10079

trace data sent/received via SQL*Net

转储SQL*NET统计信息

ALTER SESSION SET EVENTS '10079 trace name context forever, level 2';

10081

segment High Water Mark has been advanced

转储高水位标记变化

ALTER SESSION SET EVENTS '10081 trace name context forever, level 1';

10104

dump hash join statistics to trace file

转储HASH连接统计信息，用level 10

ALTER SESSION SET EVENTS '10104 trace name context forever, level 10';

10200

consistent read buffer status

转储一致性读信息

10201

consistent read undo application

转储一致性读中Undo应用

10209

enable simulated error on control file

允许在控制文件中模拟错误

10210

check data block integrity

触发数据块检查事件

event = "10210 trace name context forever, level 10"

10213

crash after control file write

模拟在写控制文件后崩溃

10214

simulate write errors on control file

模拟在控制文件中的写错误，levelnumber从1-9表示产生错误的块号，大于等于10则每个控制文件将出错

10215

simulate read errors on control file

模拟在控制文件中的读错误

10220

show updates to the transaction table

转储Undo头部变化

10221

show changes done with undo

转储Undo变化

10224

index block split/delete trace

10224事件可以分析索引块分裂及删除

alter session set events '10224 trace name context forever,level 10';

alter session set events '10224 trace name context off';

10229

simulate I/O error against datafiles

模拟在数据文件上的I/O错误

10231

skip corrupted blocks on _table_scans_

设置在全表扫描时忽略损坏的数据块

alter session set events '10231 trace name context off'; -- 关闭会话期间的数据块检查

event = "10231 trace name context forever, level 10" -- 对任何进程读入SGA的数据块进行检查

10232

dump corrupted blocks symbolically when kcbgotten

将设置为软损坏（DBMS_REPAIR包设置或DB_BLOCK_CHECKING为TRUE时设置）的数据块dump到跟踪文件

10241

remote SQL execution tracing/validation

转储远程SQL执行

10246

print trace of PMON actions to trace file

跟踪PMON进程，将PMON的动作输出到trace文件中

alter session set events '10246 trace name context forever,level 4';

alter session set events '10246 trace name context off';

10252

shared IO pool error simulation

模拟写数据文件头部错误

10253

limit SQL text returned from X$KGLNA[1]

模拟写重做日志文件错误

10270

Debug shared cursors

转储共享游标

10285

Simulate control file header corruption

模拟控制文件头部损坏

10286

Simulate control file open error

模拟控制文件打开错误

10287

Simulate archiver error

模拟归档出错

10357

turn on debug information for direct path

调试直接路径机制

10500

turn on traces for SMON

跟踪SMON进程

10608

trace create bitmap index

跟踪位图索引的创建

10622

test/trace online index (re)build

测试或转储索引的在线创建和重建

10708

print out trace information from the RAC buffer cache

跟踪RAC的Buffer Cache

28401

屏蔽密码延迟验证

ALTER SYSTEM SET EVENTS '28401 TRACE NAME CONTEXT FOREVER, LEVEL 1';--屏蔽密码延迟验证

ALTER SYSTEM SET EVENTS '28401 TRACE NAME CONTEXT OFF'; --关闭28401事件

http://mp.weixin.qq.com/s/Vm5PqNcDcITkOr9cQg6T7w