Oracle Events (Personal Reference)

oracle| Reference
Oracle Internal Events:

Introduction: (Introduction)

There are four kinds of events:

Immediate dumps

Conditional dumps

Trace dumps

Events that change database behaviour

Each event has a number that is the same as the Oracle error message. Such as 10046 and ORA-10046

Each event has a level, which can be the following:

Range 1 to 10

Bit 0x01 0x02 0x04 0x08 0x10

Identification 0=off,1=on

ID Number object ID (objects ID), memory address (memory addresses)

Note that events vary from one version to the next. There are some things that may be controversial or unusable, and often they will be replaced by new events. Also note that in the current version the message file does not necessarily reflect events.

Many events affect the behavior of the database, and some test events are most likely to cause the database to drop. So, without Oracle support, It is best not to do events on the pro system. Dev system if you want to do events, it is best to make a full backup of the database.

Enabling events (Enable event)

Events can be enabled at the instance level, mainly in Init.ora files:

Event= ' Event Trace name context forever;

(Red section: event refers to the incident number. Level specifies the rank of the event)

You can enable multiple events at once, in the following two ways:

1. Separated by a colon

event = "10248 Trace name Context forever, level 10:10249 Trace name Context forever, Level 10"

2. Two events separately written

event= "10248 Trace name Context forever, Level 10"

event= "10249 Trace name Context forever, Level" #一些版本的Oracle, event to be the same case

Events can also be used at the instance level using ALTER system commands to enable:

ALTER SYSTEM SET EVENTS ' Event trace name context forever;

At the instance level for the next disable

ALTER SYSTEM SET EVENTS ' Event Trace name context off ';

Events can also be used at the session level using ALTER SESSION commands to enable:

ALTER session SET EVENTS ' event trace name context forever;

At the session level, use the command disable:

ALTER session SET EVENTS ' event Trace name context off ';

Events in the other session with Oradebug to enable:

Implement enable in a process:

Oradebug Event Event TRACE NAME context FOREVER

Enable in a process:

Oradebug Setorapid 8 (PID process number)

Oradebug Event Event TRACE NAME context FOREVER

The following command disable:

Oradebug Event Event TRACE NAME context off

Implement enable in session:

Oradebug session_event EVENT TRACE NAME Context FOREVER

Implement Disable in session:

Oradebug session_event EVENT TRACE NAME context off

Events can also be used Dbms_system. Setev packages to implement enable and disable

(Get Sid and serial# from the V$session view before doing it)

In the following manner: EXECUTE Dbms_system. Set_ev (Sid,serial#,event,level, "")

such as Execute Dbms_system.set_ev (9,29,10046,8, "");

To disable, change the level to 0, such as: EXECUTE Dbms_system.set_ev (9,29,10046,0, ");

Listing All Events: (List all available events)

Most of the events number ranges from 10000 to 10999. You can dump all the information with the following command

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;

/

Under UNIX System The message file is in the bottom directory $oracle_home/rdbms/mesg/oraus.msg

Under NT system The message file is in the bottom directory $oracle_home/rdbms/mesg/oraus.msg

Listing enabled events (list enabled events)

Use the following command to list the Events already enabled in the current session:

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;

/

Common events Reference: (red for the most commonly used and for DBAs more useful events)

Event 10013-monitor Transaction RECOVERY------Tracking Transaction Recovery at startup

ALTER session SET EVENTS ' 10013 Trace name Context forever, Level 1 ';

Event 10015-dump Undo Segment Headers----do Dump fallback segment header information after transaction recovery

ALTER session SET EVENTS ' 10015 Trace name Context forever, Level 1 ';

Event 10032-dump Sort Statistics----Dump sort statistics, Level 10 is the most detailed

ALTER session SET EVENTS ' 10032 Trace name context forever, Level 10 ';

Event 10033-dump Sort Intermediate Run statistics-level 10 (not understood)

ALTER session SET EVENTS ' 10033 Trace name context forever, Level 10 ';

Event 10045-trace Free List Management operations-tracking Freelist

ALTER session SET EVENTS ' 10045 Trace name Context forever, Level 1 ';

Event 10046-enable SQL Statement trace---Trace sql, has execution plan, state variables and waiting statistics, level 12 most detailed

ALTER session SET EVENTS ' 10046 Trace name Context forever, Level 12 ';

Level levels refer to the following figure:



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---when parsing SQL statements, the Dump optimizer made a choice, Level 1 most detailed

ALTER session SET EVENTS ' 10053 Trace name Context forever, Level 1 ';

Reference level:

Level

Action

1

Print Statistics and computations

2

Print computations only

Event 10060-dump Predicates---(Reference http://www.juliandyke.com/)

Event 10065-restrict Library Cache Dump Output for State Object dumps-(reference http://www.juliandyke.com/)

Event 10079-dump sql*net Statistics---Dump sql*net statistics

ALTER session SET EVENTS ' 10079 Trace name Context forever, Level 2 ';

Event 10081-trace High Water Mark changes-tracking HWM changes

ALTER session SET EVENTS ' 10081 Trace name Context forever, Level 1 ';

Event 10104-dump Hash Join Statistics-dump Hash Join statistical information, with level 10

ALTER session SET EVENTS ' 10104 Trace name context forever, Level 10 ';

Event 10128-dump Partition Pruning information-dump partition table information

ALTER session SET EVENTS ' 10128 trace name context forever;

Level reference

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

In 9.0.1 or later versions, you will need to establish the following table after 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 out consistent read information

ALTER session SET EVENTS ' 10200 Trace name Context forever, Level 1 ';

Event 10201-dump consistent Read Undo application---(Don't understand)

ALTER session SET EVENTS ' 10201 Trace name Context forever, Level 1 ';

Event 10220-dump Changes to undo Header-dump the change of the Undo header information

ALTER session SET EVENTS ' 10220 Trace name Context forever, Level 1 ';

Event 10221-dump undo Changes-dump Undo Change

ALTER session SET EVENTS ' 10221 Trace name Context forever, Level 7 ';

Event 10224-dump the split and delete information of the index block splits/deletes-dump

ALTER session SET EVENTS ' 10224 Trace name Context forever, Level 1 ';

Event 10225-dump Changes to Dictionary Managed extents---Dump out of the row cache, the change of the dictionary management extents

ALTER session SET EVENTS ' 10225 Trace name Context forever, Level 1 ';

The Event 10231--setting skips a corrupted block of data when doing a full table scan (when you do exp, if you have a bad block, you can set this to skip the bad blocks when exp exports, so that some of the data can be used)

ALTER SYSTEM SET EVENTS ' 10231 Trace name context forever,level 10 ';

Event 10241-dump Remote SQL Execution-dump execution information

ALTER session SET EVENTS ' 10241 Trace name Context forever, Level 1 ';

Event 10246-trace Pmon Process---can only be done in Init.ora and cannot be done with alter system

event = "10246 Trace name Context forever, Level 1"

Event 10248-trace Dispatcher Processes---done in init.ora (9iDump to Udump directory)

event = "10248 Trace name Context forever, Level 10"

Event 10249-trace Shared Server (MTS) processes---done in init.ora (9iDump to Udump directory)

event = "10249 Trace name Context forever, Level 10"

Event 10270-debug Shared cursors-(not understood)

event = "10270 Trace name Context forever, Level 10"

Event 10299-debug prefetching---(reference http://www.juliandyke.com/)

event = "10299 Trace name Context forever, Level 1"



Event 10357-debug Direct Path---(reference http://www.juliandyke.com/)

ALTER session SET EVENTS ' 10357 Trace name Context forever, Level 1 ';

Event 10390-dump Parallel Execution Slave statistics--(reference http://www.juliandyke.com/)

ALTER session SET EVENTS ' 10390 trace name context forever;

Event 10391-dump Parallel Execution granule allocation---

(Refer to http://www.juliandyke.com/)

ALTER session SET EVENTS ' 10391 trace name context forever;

Event 10393-dump Parallel Execution statistics--(reference http://www.juliandyke.com/)

ALTER session SET EVENTS ' 10393 Trace name Context forever, Level 1 ';

Event 10500-trace Smon process--in Init.ora

event = "10500 Trace name Context forever, Level 1"

Event 10608-trace Bitmap Index creation-tracking two-bit diagram index

ALTER session SET EVENTS ' 10608 Trace name context forever, Level 10 ';

Event 10704-TRACE enqueues-Tracking Queue

ALTER session SET EVENTS ' 10704 Trace name Context forever, Level 1 ';

Event 10706-trace Global Enqueue manipulation-(reference http://www.juliandyke.com/)

ALTER session SET EVENTS ' 10706 Trace name Context forever, Level 1 ';

Event 10708-trace RAC Buffer cache-tracking the Buffer Cache of RAC

ALTER session SET EVENTS ' 10708 Trace name context forever, Level 10 ';

Event 10710-trace Bitmap Index access--(reference 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;

Levels are:

Level

Action

1

Print Parent Query and subquery

2

Print subquery Only

Event 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 ';



Some of the other events:

ALTER session SET EVENTS ' immediate trace name Flush_cache ';--flush the Buffer cache



Data source http://www.juliandyke.com/

These are some of the references that individuals do as DBAs.