remote dba support
    HomeOracle DBA TipsWhat is Event Tracing

    What is Event Tracing

    Detail explanation of What is Event Tracing and usage with accurate commands and example of event tracing in Oracle database.

    What is Event Tracing? Does it Really Help — Explained with an Example

    Yes, This question always arises in every Oracle DBA’s mind. One more question also comes in the mind and that is..

    Does Event Tracing in Oracle really helps?

    Recently, I face an “ORA-00942: table or view does not exist” which seems very tiny to me, but soon i realize that, this was not small problem, because this error was coming from the same query but intermittent, How is this possible, that same table exists once but not another time. I assume some other situations like this could be table partition issue, but all my assumption were not helping at all.

    - Advertisement -

    Finally, I decide to Trace an Event for “ORA-00942” message and then solve the issue, which was due to a bug in Oracle. What if, I had not traced the event, definitely i was not able to overcome this challenge. Yes, Event tracing really helps …

    What is Event Tracing?

    I believe every DBA has seen ORA-00600 trace file generated every time ORA-00600 or ORA-07445 occurs which has each and every detail about this error. Generating trace files for ORA-00600 or ORA-07445 is the default behaviour of Oracle, but the Same doesn’t happen in case of other errors.

    To solve any ORA-00600 or ORA-07445, Database Administrator analyze its call stack and try to match it with some bug and finds either some workaround or patch to fix this.

    So Event Tracing is basically noting down each and every step for the execution of a particular query from starting up to when an issue or error Reoccurs in a file, which can be analyzed for troubleshooting.

    - Advertisement -

    Let’s take an Example to understand Tracing an Event in Oracle:- In the below example, I am generating an “ORA-01422” error and will trace it to find out root cause for the error.

    SQL> select * from dept;

    DEPTNO DNAME          LOC
    ———- ————– ————-
    20 RESEARCH       DALLAS
    30 SALES          CHICAGO
    40 operation      BOSTON

    SQL> insert into dept values(31,'SALES','USA');

    1 row created.

    SQL> commit;

    Commit complete.

    SQL> create or replace procedure test_proc1 as
    v_two    varchar2(5);
    select DNAME into v_two from dept where DNAME = 'SALES';

    Procedure created.

    SQL> exec test_proc;

    BEGIN test_proc1; END;


    ERROR at line 1:
    ORA-01422: exact fetch returns more than requested number of rows
    ORA-06512: at “SCOTT.TEST_PROC1”, line 8
    ORA-06512: at line 1

    This is a very small piece of code, What if this has been a code of thousands of lines. It has been very difficult for Remote DBA to find out which sql query or table is generating this error message.

    But DBA can easily find out culprit command or table by setting up tracing on “ORA-01422: exact fetch returns more than requested number of rows” (Error messages are called as events)

    How to Trace an Event ?

    For event tracing DBA has to use the “alter system/session set event {event_id} trace name {action} {parameter}” command. Unix Find command is used to find event file generated at OS level. In the below steps, Database Administrator is tracing “ORA-01422: exact fetch returns more than requested number of rows” and locating trace file with Linux find command. DBA has to first set the event for tracing then Re execute the query generating the issue to catch the event.

    SQL> connect sys as sysdba
    Enter password:

    System altered.

    SQL> set linesize 1000
    SQL> show parameter user_dump_dest
    ———————————— ——————————– ——————————
    user_dump_dest string /etc/oracle/diag/rdbms/orcl/orcl/trace

    SQL> connect scott
    Enter password:

    SQL> exec test_proc1;

    BEGIN test_proc; END;
    ERROR at line 1:
    ORA-01422: exact fetch returns more than requested number of rows
    ORA-06512: at “SCOTT.TEST_PROC1”, line 8
    ORA-06512: at line 1

    [oracle@database ~]$ cd /etc/oracle/diag/rdbms/orcl/orcl/trace
    [oracle@database trace]$ find . -type f -print | xargs grep -li “ORA-01422” ./alert_orcl.log ./orcl_ora_3982.trc

    Here is the message from Alert log and trace file generated from a database, Alert log shows name of trace file as well.

    Sat Jun 08 23:58:49 2020
    OS Pid: 3295 executed alter system set events ‘1422 TRACE NAME ERRORSTACK LEVEL 3’
    Sat Jun 08 23:59:02 2020
    Errors in file /etc/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_3982.trc:
    ORA-01422: exact fetch returns more than requested number of rows
    Sat Jun 08 23:59:11 2020
    Dumping diagnostic data in directory=[cdmp_20130608235911], requested by (instance=1, osid=3982), summary=[abnormal process termination].
    “alert_orcl.log” 3253L, 137636C

    Trace file orcl_ora_3982.trc

    Trace file /etc/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_3982.trc
    Oracle Database 11g Enterprise Edition Release – Production
    With the Partitioning, OLAP, Data Mining and Real Application Testing options
    ORACLE_HOME = /etc/oracle/oracle
    System name: Linux
    Node name:
    Release: 2.6.18-194.el5
    Version: #1 SMP Mon Mar 29 20:06:41 EDT 2020
    Machine: i686
    Instance name: orcl
    Redo thread mounted by this instance: 1
    Oracle process number: 20
    Unix process pid: 3982, image: [email protected] (TNS V1-V3)
    ** 2020-06-08 23:59:02.829
    *** SESSION ID:(45.27) 2020-06-08 23:59:02.829
    *** CLIENT ID:() 2020-06-08 23:59:02.829
    *** SERVICE NAME:(SYS$USERS) 2020-06-08 23:59:02.829
    *** MODULE NAME:(SQL*Plus) 2020-06-08 23:59:02.829
    *** ACTION NAME:() 2020-06-08 23:59:02.829
    dbkedDefDump(): Starting a non-incident diagnostic dump (flags=0x0, level=3, mask=0x0)
    —– Error Stack Dump —–
    ORA-01422: exact fetch returns more than requested number of rows
    — Current SQL Statement for this session (sql_id=9hts4bbxq1k14) —–
    —- PL/SQL Stack —–
    —– PL/SQL Call Stack —–
    object line object
    handle number name
    0x3de13254 8 procedure SCOTT.TEST_PROC1

    From the above trace file DBA can easily identify for “Current SQL Statement for this session” is “SELECT DNAME FROM DEPT WHERE DNAME = ‘SALES'” which is causing “ORA-01422: exact fetch returns more than requested number of rows” error message. Call stack is also available for this query is trace file, Database Administrator can also analyze call stack for other issues.

    This is how tracing helps in Oracle troubleshooting. Now, let’s talk about the detail of tracing parameters and diff-2 levels in event tracing.

    Syntax for Tracing and Event =>



    alter SYSTEM set events ‘WHAT TO TRACE?‘ scope = spfile;
    alter SESSION set events ‘WHAT TO TRACE?‘;

    ALTER SYSTEM set events ‘904 trace name ERRORSTACK LEVEL 3’;
    ALTER SESSION set events ‘10046 trace name CONTEXT FOREVER, LEVEL 12’;

    In above two examples DBA is tracing “ORA-00904: invalid identifier” error and Enable standard SQL_TRACE functionality for sql queries. These are called as events.

    Now, The most common Actions in tracing an event are:

    ERRORSTACK – This action with produce session errorstack trace as well see in the above example, This is used for tracing errors in code etc.
    SYSTEMSTATE – System state dump is taken in case of database hang situation.
    CONTEXT – configure some behavior. I have not seen so much use of this.

    Examples of Actions:

    ALTER SYSTEM set events '904 trace name ERRORSTACK LEVEL 3';
    This setting causes an ERRORSTACK dump when an ORA-904 occurs.

    ALTER SYSTEM set events 'IMMEDIATE trace name SYSTEMSTATE LEVEL 266';
    This setting causes a SYSTEMSTATE dump to occur when the command is issued.

    ALTER SESSION set events '10046 trace name CONTEXT FOREVER, LEVEL 12';
    This setting enables level 12 SQL Tracing.

    Parameters for Event Tracing

    Every action has it’s own parameters, which are given below:

    ERRORSTACK Parameters:

    0 – Error stack only
    1 – Error stack and function call stack (if implemented)
    2 – As 1 plus the ProcessState
    3 – As 2 plus the context area (all cursors and current cursor highlighted)

    DBA’s Usually prefer to use 3 as the parameter of tracing, Since it includes all details.

    SYSTEMSTATE parameters:

    1 – Very basic process information only
    10 – Most common level – includes state object trees for all processes. Includes “interesting” RAC resources in the trace (in RAC only).
    11 – In RAC level 11 includes a dump of any resources not on the freelist just after the “BUSY GLOBAL CACHE ELEMENTS” section.
    LEVEL + 256 – Adding 256 to the level will try to dump short stack info for eac

    CONTEXT parameters:

    CONTEXT only seems to have two parameters:
    FOREVER – enables the event until disabled
    OFF – disables the event

    Enabling Tracing at Different Levels:

    Database Administrators can enable Event tracing at the System or session level, or both. Enabling tracing at the system level or permanently needing an instance restart. DBA has to add scope=spfile at end of trace command like

    ALTER SYSTEM set events '904 trace name errorstack level 3' SCOPE=SPFILE;

    Enabling tracing at the Session level will take effect immediately and this is default as well, DBA has not to give any scope parameter for this.

    alter SYSTEM set events '1042 trace name errorstack level 3' SCOPE = MEMORY;
    alter SESSION set events '1042 trace name errorstack level 1';

    To set event at both Spfile and Memory use scope=both;

    Once you have done with tracing don’t forget to disable it otherwise it will generate lot of files at user_dump_dest location and mount point will be full.

    Disabling Event Tracing:

    alter session set events '1555 trace name errorstack off';

    Tracing an Event helps me a lot to solve my day to day issue. Stay connected with our Expert DBA Team Club blog for gaining more Oracle DBA Tutorials. This blog is running by Dbametrix for providing latest database news and tutorials.

    When you want to make a strong Oracle DBA career then you should be aware of database services and other database technology. Without having knowledge of Oracle internals, Oracle performance tuning, and skill of Oracle database troubleshooting you can’t be an Oracle DBA expert.

    This expert DBA Team club blog always provides you latest technology news and database news to keep yourself up to date. You should need to be aware of Cloud database technology like DBaaS. All Oracle DBA tips are available in a single unique resource at our orageek. Meanwhile, we are also providing some sql tutorials for Oracle DBA. This is the part of Dbametrix Group and you would enjoy more advanced topics from our partner resource.

    - Advertisement -
    - Advertisment -
    remote dba services

    Most Popular