Forgot your password?    
+ Reply to Thread
Results 1 to 3 of 3

Thread: How to trace a Database session?

  1. #1

    Post How to trace a Database session?

    There are numerous ways to enable, disable and vary the contents of this trace. The following methods have been available for several versions of the database.

    SQL> EXEC DBMS_SESSION.set_sql_trace(sql_trace => TRUE);
    SQL> EXEC DBMS_SESSION.set_sql_trace(sql_trace => FALSE);
    SQL> ALTER SESSION SET EVENTS '10046 trace name context forever, level 8';
    SQL> ALTER SESSION SET EVENTS '10046 trace name context off';
    SQL> EXEC DBMS_SYSTEM.set_sql_trace_in_session(sid=>123, serial#=>1234, sql_trace=>TRUE);
    SQL> EXEC DBMS_SYSTEM.set_sql_trace_in_session(sid=>123, serial#=>1234, sql_trace=>FALSE);
    SQL> EXEC DBMS_SYSTEM.set_ev(si=>123, se=>1234, ev=>10046, le=>8, nm=>' ');
    SQL> EXEC DBMS_SYSTEM.set_ev(si=>123, se=>1234, ev=>10046, le=>0, nm=>' ');

    All versions, requires DBMS_SUPPORT package to be loaded.

    SQL> EXEC DBMS_SUPPORT.start_trace(waits=>TRUE, binds=>FALSE);
    SQL> EXEC DBMS_SUPPORT.stop_trace;
    SQL> EXEC DBMS_SUPPORT.start_trace(sid=>123, serial=>1234, waits=>TRUE, binds=>FALSE);
    SQL> EXEC DBMS_SUPPORT.stop_trace(sid=>123, serial=>1234);

    The dbms_support package is not present by default, but can be loaded as the SYS user by executing the @$ORACLE_HOME/rdbms/admin/dbmssupp.sql script.

    Last edited by Hemant; 01-15-2012 at 09:33 PM.

  2. #2
    nice one bro...

  3. #3
    Expert Oracle Administrator
    Join Date
    Oct 2011
    New Delhi, India
    Trace a session using DBMS_MONITOR

    EXEC DBMS_MONITOR.session_trace_enable(session_id=>161, serial_num=>30308,waits=>TRUE, binds=>FALSE);
    PL/SQL procedure successfully completed.

    EXEC DBMS_MONITOR.session_trace_disable;
    PL/SQL procedure successfully completed.

    TRACE files Generated :
    -rw-r----- 1 oradev dba 4422 Sep 10 16:43 DEV_ora_12101.trm
    -rw-r----- 1 oradev dba 413913 Sep 10 16:43 DEV_ora_12101.trc

+ Reply to Thread

Similar Threads

  1. Generate an FRD (Forms Runtime Diagnostics) Trace for any Form in Oracle Applications
    By Amit in forum Concepts,Service Management,Apache,OC4J,OPMN,JServ,Forms, Issues,Troubleshooting,
    Replies: 12
    Last Post: 05-10-2012, 04:00 PM
  2. Get the OS Process ID from the SID for a Database session
    By Hemant in forum Database Scripts
    Replies: 0
    Last Post: 11-02-2011, 07:41 PM
  3. Replies: 0
    Last Post: 11-02-2011, 07:29 PM
  4. Retrieve Profile Options Where Trace is Enabled
    By Hemant in forum SCRIPTS : Users,Profiles,Applications,Responsibilities,Forms,JDBC,Patch,Versions,Products
    Replies: 0
    Last Post: 11-01-2011, 11:04 AM
  5. Trace File for a Concurrent Request Id
    By Hemant in forum SCRIPTS : Concurrent Requests & Concurrent Request Sessions
    Replies: 0
    Last Post: 10-31-2011, 08:26 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

DBA Lounge (P) Ltd. deals in Oracle Technologies on Consulting, Resourcing, Corporate Training

Online and corporate training available on Oracle Database 11g, Oracle 11g Real Application Cluster (RAC), Oracle Applications 11i/R12, Oracle Fusion Middleware 11g, Oracle Identity Management-OIM, Oracle Internet Directory 11g-OID, Oracle Business Intelligence Enterprise Edition-OBIEE, Oracle Golden Gate, Oracle Access Management-OAM, Oracle Internet Directory-ODS, Oracle Identity Analytics Architecture-OIA Statistics