時(shí)間：2015/6/28來(lái)源：IT貓撲網(wǎng)作者：網(wǎng)管聯(lián)盟我要評(píng)論(0)
Events事件是oracle的重要診斷工具及問(wèn)題解決辦法，很多時(shí)候需要通過(guò)Events設(shè)置來(lái)屏蔽或者更改Oracle的行為;
有四種類型的Events:
Immediate dumps
Conditional dumps
Trace dumps
Events that change database behaviour
每一個(gè)事件都有一個(gè)號(hào)跟Oracle的錯(cuò)誤信息是一樣的.如10046和ORA-10046
每一個(gè)事件都有一個(gè)Level,可以是以下:
范圍1到10
位標(biāo) 0x01 0x02 0x04 0x08 0x10
標(biāo)識(shí) 0=off,1=on
ID號(hào) 對(duì)象ID(object id),內(nèi)存地址(memory address)
要注意的是,Events在每一個(gè)版本之間都有所改變.有一些存在的事件可能存在爭(zhēng)議性或者已經(jīng)不可用了,往往這些事件號(hào)會(huì)由新的事件所替代掉.也要注意在當(dāng)前的版本中message file不一定可以反映出Events.
很多Events都會(huì)影響數(shù)據(jù)庫(kù)的行為,一些測(cè)試Events極有可能導(dǎo)致數(shù)據(jù)庫(kù)DOWN掉.所以,在沒(méi)有Oracle Support的前提下,最好不要在PRO系統(tǒng)上做Events操作.DEV系統(tǒng)上如果要做Events最好先做個(gè)數(shù)據(jù)庫(kù)的全備份.
Enabling Events(Enable事件)
Events可以在Instance一級(jí)Enabled,主要是在INIT.ORA文件中做操作:
event='event trace name context forever, level level';
(紅色部分:event指事件號(hào).level指定事件的級(jí)別)
一次可以Enable多個(gè)事件,可以用以下兩種方式:
1. 用一個(gè)冒號(hào)隔開(kāi)
event = "10248 trace name context forever, level 10:10249 trace name context forever, level 10"
2. 兩個(gè)Events分開(kāi)寫
event="10248 trace name context forever, level 10"
event="10249 trace name context forever, level 10"#一些版本的Oracle，event要一樣的大小寫
Events也可以在Instance一級(jí)用ALTER SYSTEM命令來(lái)Enable:
ALTER SYSTEM SET EVENTS 'event trace name context forever, level level';
在Instance一級(jí)用以下Disable
ALTER SYSTEM SET EVENTS 'event trace name context off';
Events也可以在Session一級(jí)用ALTER SESSION命令來(lái)Enable:
ALTER SESSION SET EVENTS 'event trace name context forever, level level';
在Session一級(jí)用以下命令Disable:
ALTER SESSION SET EVENTS 'event trace name context off';
Events在其他的Session用ORADEBUG來(lái)Enable:
在一個(gè)Process中實(shí)現(xiàn)Enable:
ORADEBUG EVENT event TRACE NAME CONTEXT FOREVER, LEVEL level
在某個(gè)進(jìn)程中Enable:
ORADEBUG SETORAPID 8(PID進(jìn)程號(hào))
ORADEBUG EVENT event TRACE NAME CONTEXT FOREVER, LEVEL level
以下命令Disable:
ORADEBUG EVENT event TRACE NAME CONTEXT OFF
在Session中實(shí)現(xiàn)Enable:
ORADEBUG SESSION_EVENT event TRACE NAME CONTEXT FOREVER, LEVEL level
在Session中實(shí)現(xiàn)Disable:
ORADEBUG SESSION_EVENT event TRACE NAME CONTEXT OFF
Events也可以用DBMS_SYSTEM.SETEV包來(lái)實(shí)現(xiàn)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 ON
DECLARE
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;
/
#p#副標(biāo)題#e#
在UNIX系統(tǒng)下message文件在底下目錄$ORACLE_HOME/rdbms/mesg/oraus.msg
在NT系統(tǒng)下message文件在底下目錄$ORACLE_HOME/rdbms/mesg/oraus.msg
Listing Enabled Events(列出Enabled Events)
用以下命令列出在當(dāng)前Session已經(jīng)Enabled Events:
SET linesize 120
SET feedback OFF
SET SERVEROUTPUT ON
DECLARE 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;
/
常用Events的參考:(紅色的為最常用的而且對(duì)DBA比較有用的Events)
Event 10013 - Monitor Transaction Recovery------在Startup時(shí)跟蹤事務(wù)恢復(fù)
ALTER SESSION SET EVENTS '10013 trace name context forever, level 1';
Event 10015 - Dump Undo Segment Headers----在事務(wù)恢復(fù)后做Dump回退段頭信息
ALTER SESSION SET EVENTS '10015 trace name context forever, level 1';
Event 10032 - Dump Sort Statistics----Dump排序的統(tǒng)計(jì)信息,level 10是最詳細(xì)的
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,有執(zhí)行計(jì)劃,邦定變量和等待的統(tǒng)計(jì)信息,level 12最詳細(xì)
ALTER SESSION SET EVENTS '10046 trace name context forever, level 12';
級(jí)別level參考如下圖:
Level Action
1 Print SQL statements, execution plans and execution statistics
4 As level 1 plus bind variables
8 As level 1 plus wait statistics
12 As level 1 plus bind variables and wait statistics
Event 10053 - Dump Optimizer Decisions---在分析SQL語(yǔ)句時(shí),Dump出優(yōu)化器所做的選擇,級(jí)別level 1最詳細(xì)
ALTER SESSION SET EVENTS '10053 trace name context forever, level 1';
參考level:
Level Action
1 Print statistics and computations
2 Print computations only
Event 10060 - Dump Predicates---(參考https://www.juliandyke.com/)
Event 10065 - Restrict Library Cache Dump Output for State Object Dumps-(參考https://www.juliandyke.com/)
Event 10079 - Dump SQL*Net Statistics---Dump SQL*NeT的統(tǒng)計(jì)信息
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的統(tǒng)計(jì)信息,用level 10
ALTER SESSION SET EVENTS '10104 trace name context forever, level 10';
Event 10128 - Dump Partition Pruning Information—Dump分區(qū)表信息
ALTER SESSION SET EVENTS '10128 trace name context forever, level level';
Level參考
Level Action
0x0001 Dump pruning descriptor for each partitioned object
0x0002 Dump partition iterators
0x0004 Dump optimizer decisions about partition-wise joins
0x0008 Dump ROWID range scan pruning information
關(guān)鍵詞標(biāo)簽：Oracle Events
相關(guān)閱讀
熱門文章
Oracle中使用alter table來(lái)增加,刪除,修改列
oracle中使用SQL語(yǔ)句修改字段類型-oracle修
使用低權(quán)限Oracle數(shù)據(jù)庫(kù)賬戶得到管理員權(quán)限
Oracle對(duì)user的訪問(wèn)控制
人氣排行 ORACLE SQL 判斷字符串是否為數(shù)字的語(yǔ)句Oracle中使用alter table來(lái)增加,刪除,修改列的語(yǔ)法ORACLE和SQL語(yǔ)法區(qū)別歸納(1)oracle grant 授權(quán)語(yǔ)句如何加速Oracle大批量數(shù)據(jù)處理Oracle刪除表的幾種方法ORACLE修改IP地址后如何能夠使用Oracle 10g創(chuàng)建表空間和用戶并指定權(quán)限