Oracle DBAs - Scripts

Sumit Kumar

1. How to generate trace 10046 database trace for a given session with TKPROF?


  • Find SPID (OS process ID) for the database session using below query.

    • select sid, pid, spid, p.username from v$process p, v$session s where p.addr = s.paddr and s.username='&db_user';
    or
    select sid, pid, spid, p.username from v$process p, v$session s where p.addr = s.paddr and s.sid='&session_id';

    PID SPID USERNAME
    ---------- ------------------------ ---------------
    42 19602 oracle
    43 28463 oracle
  • Connect as sysdba and enable 10046 trace for identified spid

    sqlplus / as sysdba
    alter system set max_dump_file_size = unlimited ;
    ORADEBUG SETOSPID 6007
    ORADEBUG EVENT 10046 TRACE NAME CONTEXT FOREVER, LEVEL 12
    oradebug tracefile_name
  • Turn off trace once you have enough information captured in trace file. This can be turned off after 15-20mins of trace enablement

    • sqlplus / as sysdba
    ORADEBUG SETOSPID 6007
    ORADEBUG EVENT 10046 TRACE NAME CONTEXT OFF
  • Generate the TKPROF output and check the output file for details of session

    tkprof dbtst14_ora_123289.trc dbtst14_ora_123289.log explain=system/password sys=no sort=prsela,exeela,fchela