Oracle Internal Events:IntrodUCtion:(簡介)有四種類型的Events: Immediate dumps ;;;;Conditional dumps Trace dumps Events that change database behaviour每一個事件都有一個號跟Oracle的錯誤信息是一樣的.如10046和ORA-10046每一個事件都有一個Level,可以是以下: 范圍1到10 位標 0x01 0x02 0x04 0x08 0x10 標識 0=off,1=on ID號 對象ID(object id),內存地址(memory address)要注重的是,Events在每一個版本之間都有所改變.有一些存在的事件可能存在爭議性或者已經不可用了,往往這些事件號會由新的事件所替代掉.也要注重在當前的版本中message file不一定可以反映出Events.很多Events都會影響數據庫的行為,一些測試Events極有可能導致數據庫DOWN掉.所以,在沒有Oracle Support的前提下,最好不要在PRO系統上做Events操作.DEV系統上假如要做Events最好先做個數據庫的全備份.Enabling Events(Enable事件)Events可以在Instance一級Enabled,主要是在INIT.ORA文件中做操作: event='event trace name context forever, level level';(紅色部分:event指事件號.level指定事件的級別) 一次可以Enable多個事件,可以用以下兩種方式:1. ;用一個冒號隔開 event = '10248 trace name context forever, level 10:10249 trace name context forever, level 10'2. ;兩個Events分開寫 event='10248 trace name context forever, level 10' event='10249 trace name context forever, level 10'#一些版本的Oracle，event要一樣的大小寫Events也可以在Instance一級用ALTER SYSTEM命令來Enable: ALTER SYSTEM SET EVENTS 'event trace name context forever, level level';　在Instance一級用以下Disable ;ALTER SYSTEM SET EVENTS 'event trace name context off';Events也可以在Session一級用ALTER SESSION命令來Enable: ;ALTER SESSION SET EVENTS 'event trace name context forever, level level';在Session一級用以下命令Disable:ALTER SESSION SET EVENTS 'event trace name context off'; Events在其他的Session用ORADEBUG來Enable:在一個Process中實現Enable: ORADEBUG EVENT event TRACE NAME CONTEXT FOREVER, LEVEL level在某個進程中Enable: ;ORADEBUG SETORAPID 8(PID進程號) ORADEBUG EVENT event TRACE NAME CONTEXT FOREVER, LEVEL level以下命令Disable:ORADEBUG EVENT event TRACE NAME CONTEXT OFF在Session中實現Enable:ORADEBUG SESSION_EVENT event TRACE NAME CONTEXT FOREVER, LEVEL level在Session中實現Disable: ORADEBUG SESSION_EVENT event TRACE NAME CONTEXT OFFEvents也可以用DBMS_SYSTEM.SETEV包來實現Enable和Disable ;(在做之前要先從V$session視圖中獲得SID和Serial#)用以下方式:EXECUTE DBMS_SYSTEM.SET_EV(SID,Serial#,event,level, '')如EXECUTE dbms_system.set_ev (9,29,10046,8,''); 要Disable則將level改為0,如: EXECUTE dbms_system.set_ev (9,29,10046,0,'');Listing All Events:(列出所有可用的Events)大部分的Events number的范圍都在10000到10999.可以用以下命令Dump出所有的信息SET SERVEROUTPUT ONDECLARE err_msg VARCHAR2(120);BEGIN ;dbms_output.enable (1000000); FOR err_num IN 10000..10999 LOOP ;err_msg := SQLERRM (-err_num); ;IF err_msg NOT LIKE '%Message 'err_num' not found%' THEN ;dbms_output.put_line (err_msg); ;END IF; END LOOP;END;/在UNIX系統下message文件在底下目錄$ORACLE_HOME/rdbms/mesg/oraus.msg在NT系統下message文件在底下目錄$ORACLE_HOME/rdbms/mesg/oraus.msgListing Enabled Events(列出Enabled Events) 用以下命令列出在當前Session已經Enabled Events: SET SERVEROUTPUT ON ;DECLARE l_level NUMBER; BEGIN FOR l_event IN 10000..10999 LOOP dbms_system.read_ev (l_event,l_level); ;IF l_level > 0 THEN dbms_output.put_line ('Event 'TO_CHAR (l_event) ' is set at level 'TO_CHAR (l_level)); END IF; END LOOP; END; ;/常用Events的參考:(紅色的為最常用的而且對DBA比較有用的Events)Event 10013 - Monitor Transaction Recovery------在Startup時跟蹤事務恢復 ;ALTER SESSION SET EVENTS '10013 trace name context forever, level 1';Event 10015 - Dump Undo Segment Headers----在事務恢復后做Dump回退段頭信息 ;ALTER SESSION SET EVENTS '10015 trace name context forever, level 1';Event 10032 - Dump Sort Statistics----Dump排序的統計信息,level 10是最具體的 ;ALTER SESSION SET EVENTS '10032 trace name context forever, level 10';Event 10033 - Dump Sort Intermediate Run Statistics—level 10(不明白) ;ALTER SESSION SET EVENTS '10033 trace name context forever, level 10';Event 10045 - Trace Free List Management Operations—跟蹤Freelist ;ALTER SESSION SET EVENTS '10045 trace name context forever, level 1';Event 10046 - Enable SQL Statement Trace---跟蹤SQL,有執行計劃,邦定變量和等待的統計信息,level 12最具體 ;ALTER SESSION SET EVENTS '10046 trace name context forever, level 12';級別level參考如下圖:LevelAction1Print SQL statements, execution plans and execution statistics4As level 1 plus bind variables8As level 1 plus wait statistics12As level 1 plus bind variables and wait statisticsEvent 10053 - Dump Optimizer Decisions---在分析SQL語句時,Dump出優化器所做的選擇,級別level 1最具體 ;ALTER SESSION SET EVENTS '10053 trace name context forever, level 1';參考level:LevelAction1Print statistics and computations2Print computations onlyEvent 10060 - Dump Predicates---(參考http://www.juliandyke.com/)Event 10065 - Restrict Library Cache Dump Output for State Object Dumps-(參考http://www.juliandyke.com/)Event 10079 - Dump SQL*Net Statistics---Dump SQL*NeT的統計信息 ALTER SESSION SET EVENTS '10079 trace name context forever, level 2';Event 10081 - Trace High Water Mark Changes—跟蹤HWM的改變 ALTER SESSION SET EVENTS '10081 trace name context forever, level 1';Event 10104 - Dump Hash Join Statistics—Dump HASH JOIN的統計信息,用level 10 ;ALTER SESSION SET EVENTS '10104 trace name context forever, level 10';Event 10128 - Dump Partition Pruning Information—Dump分區表信息 ;ALTER SESSION SET EVENTS '10128 trace name context forever, level level'; Level參考LevelAction0x0001Dump pruning descriptor for each partitioned object0x0002Dump partition iterators0x0004Dump optimizer decisions about partition-wise joins0x0008Dump ROWID range scan pruning information在9.0.1或者后面的版本,在level 2后還需要建立如下的表:CREATE TABLE kkpap_pruning ( partition_count NUMBER, iterator VARCHAR2(32), partition_level VARCHAR2(32), order_pt VARCHAR2(12), call_time VARCHAR2(12), part# NUMBER, subp# NUMBER, abs# NUMBER );Event 10200 - Dump Consistent Reads---Dump出一致讀的信息 ;ALTER SESSION SET EVENTS '10200 trace name context forever, level 1';Event 10201 - Dump Consistent Read Undo Application---(不明白) ;ALTER SESSION SET EVENTS '10201 trace name context forever, level 1';Event 10220 - Dump Changes to Undo Header—Dump出Undo頭信息的改變 ;ALTER SESSION SET EVENTS '10220 trace name context forever, level 1';Event 10221 - Dump Undo Changes—Dump Undo的改變 ;ALTER SESSION SET EVENTS '10221 trace name context forever, level 7';Event 10224 - Dump Index Block Splits / Deletes—Dump索引塊的Split和Delete信息 ;ALTER SESSION SET EVENTS '10224 trace name context forever, level 1';Event 10225 - Dump Changes to Dictionary Managed Extents---Dump出在Row Cache中,字典治理的Extents的改變 ;;;;ALTER SESSION SET EVENTS '10225 trace name context forever, level 1';Event 10231--設置在做全表掃描時跳過損壞的數據塊（在做EXP時，假如有壞塊，設置這個可以使EXP導出時跳過壞塊，使得部分數據能可以使用） ALTER SYSTEM SET EVENTS '10231 trace name context forever,level 10';Event 10241 - Dump Remote SQL Execution—Dump遠程SQL語句的執行信息 ;ALTER SESSION SET EVENTS '10241 trace name context forever, level 1';Event 10246 - Trace PMON Process---只能在init.ora中做,不能用ALTER SYSTEM做 ;event = '10246 trace name context forever, level 1'Event 10248 - Trace Dispatcher Processes---在init.ora中做(9iDump到udump目錄中) event = '10248 trace name context forever, level 10'Event 10249 - Trace Shared Server (MTS) Processes---在init.ora中做(9iDump到udump目錄中) event = '10249 trace name context forever, level 10'Event 10270 - Debug Shared Cursors—(不明白)event = '10270 trace name context forever, level 10'Event 10299 - Debug Prefetching---(參考http://www.juliandyke.com/) ;event = '10299 trace name context forever, level 1'Event 10357 - Debug Direct Path---(參考http://www.juliandyke.com/) ;ALTER SESSION SET EVENTS '10357 trace name context forever, level 1';Event 10390 - Dump Parallel Execution Slave Statistics--(參考http://www.juliandyke.com/) ;ALTER SESSION SET EVENTS '10390 trace name context forever, level level';Event 10391-Dump Parallel Execution Granule Allocation--- (參考http://www.juliandyke.com/) ;ALTER SESSION SET EVENTS '10391 trace name context forever, level level';Event 10393 - Dump Parallel Execution Statistics--(參考http://www.juliandyke.com/) ;;;;ALTER SESSION SET EVENTS '10393 trace name context forever, level 1';Event 10500 - Trace SMON Process--在init.ora中做 ;;event = '10500 trace name context forever, level 1'Event 10608 - Trace Bitmap Index Creation—跟蹤二位圖索引 ;ALTER SESSION SET EVENTS '10608 trace name context forever, level 10';Event 10704 - Trace Enqueues—跟蹤隊列 ALTER SESSION SET EVENTS '10704 trace name context forever, level 1';Event 10706 - Trace Global Enqueue Manipulation-(參考http://www.juliandyke.com/) ;ALTER SESSION SET EVENTS '10706 trace name context forever, level 1';Event 10708 - Trace RAC Buffer Cache—跟蹤RAC的Buffer Cache ALTER SESSION SET EVENTS '10708 trace name context forever, level 10';Event 10710 - Trace Bitmap Index Access--(參考http://www.juliandyke.com/) ALTER SESSION SET EVENTS '10710 trace name context forever, level 1';Event 10711 - Trace Bitmap Index Merge Operation— ALTER SESSION SET EVENTS '10711 trace name context forever, level 1';Event 10712 - Trace Bitmap Index OR Operation— ;ALTER SESSION SET EVENTS '10712 trace name context forever, level 1';Event 10713 - Trace Bitmap Index AND Operation— ;ALTER SESSION SET EVENTS '10713 trace name context forever, level 1';Event 10714 - Trace Bitmap Index MINUS Operation— ALTER SESSION SET EVENTS '10714 trace name context forever, level 1';Event 10715 - Trace Bitmap Index Conversion to ROWIDs Operation— ALTER SESSION SET EVENTS '10715 trace name context forever, level 1';Event 10716 - Trace Bitmap Index Compress/Decompress— ;ALTER SESSION SET EVENTS '10716 trace name context forever, level 1';Event 10717 - Trace Bitmap Index Compaction— ALTER SESSION SET EVENTS '10717 trace name context forever, level 1';Event 10719 - Trace Bitmap Index DML— ALTER SESSION SET EVENTS ' 10719 trace name context forever, level 1';Event 10730 - Trace Fine Grained Access Predicates— ;ALTER SESSION SET EVENTS '10730 trace name context forever, level 1';Event 10731 - Trace CURSOR Statements— ;ALTER SESSION SET EVENTS '10731 trace name context forever, level level';Levels are:LevelAction1Print parent query and subquery2Print subquery onlyEvent 10928 - Trace PL/SQL Execution— ;ALTER SESSION SET EVENTS '10928 trace name context forever, level 1';Event 10938 - Dump PL/SQL Execution Statistics— ;ALTER SESSION SET EVENTS '10938 trace name context forever, level 1';一些其他的Events：ALTER SESSION SET EVENTS 'immediate trace name flush_cache';--Flush the Buffer cache資料來源http://www.juliandyke.com/以上為個人在做DBA時的一些參考資料.