ITPub博客

首页 > Linux操作系统 > Linux操作系统 > Oracle trace event

Oracle trace event

原创 Linux操作系统 作者:licheng79 时间:2011-09-15 12:34:46 0 删除 编辑

DBA Notes: 2011/09/14

Cheng Li

 Oracle notes: sharing a article about oracle events

 

Reference of Oracle event dump

 

1 Oracle background traces files

       Oracle has three types of trace files:

1)     Background Alert file

Alert file records database startup, shut down, and other DDL activities, such as tablespace creation, rollback segment creation, alert command, log switch, error message, etc.

Storage Path: BACKGROUND_DUMP_DEST

File format: SIDALRT.LOG

 

2)     Background trace file

Background trace file records background process such as DBWR.

Storage Path: BACKGROUND_DUMP_DEST

File format: siddbwr.trcsidsmon.trc, etc

3)  User trace file

User trace file records user process for error message or warning

Storage path: USER_DUMP_DEST

File format: oraxxxxx.trc(xxxxx, process or thread number)

 

2 Oracle trace events

 

Oracle provides command interface to enable the functionalities to dump internal process information to trace. And trace is used to debug and issues resolving.

1)        Set up trace event in init.ora file. After database startup, all sessions will be traced. Usage: EVENT="eventnumber trace name eventname [forever,] [level levelnumber] : ......."

: is used for define and separate different trace event.

 

2)        Set up in session: set up trace event in session only affect current session. Usage: alter session set events '[eventnumber|immediate] trace name eventname [forever] [, level

levelnumber] : .......'

 

Usage Format:

eventnumber, event number to trigger dump event, such as Oracle error number(when Oracle generate related errors to trigger trace event) or Oracle internal event. Internal event is numbered between: 10000 and 10999. Internal event cant be used with immediate key word. When immediate key word is used, trace event is triggered immediate. This key word is only used in session, and it is not used with other key words including eventnumberforever.

 

trace name,key word

 

eventname: event name to be dumped. If the eventname is defined as context means internal event is traced.

 

Forever: forever key words means the trace event is active for whole session.

 

Levelnumber: means event is leveled as 1 to 10.1 means only header is dumped. And 10 means dump all information.

 

3 Oracle internal event numbers

 

Oracle internal defined event can be traced also, such as

 

10046trace SQL language

alter session set events '10046 trace name context forever, level 4'; --trace SQL for binding variable

alter session set events '10046 trace name context forever, level 8'; --trace SQL for waiting event

 

10262allow memory leakage during session connection

alter session set events '10262 trace name context forever, level 300'; -- allow 300 bytes memory leakage.

6210676-390x450.jpg

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/26136400/viewspace-707478/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2011-09-07

  • 博文量
    54
  • 访问量
    72349