Alejandro Vargas' Blog

  • December 5, 2006

How to Setup Tracing for Code Check

Alejandro Vargas
Technical Leader, ACS Global Delivery, Infrastructure & BigData

- If you have access to the oracle account in unix the you will be able to check the trace also, use tkprof <input file> <output file>, check waits and bind values inside the trace if needed.

- If you don't have access to the oracle account in Unix, you will need to send to an authorized user the name of the database where you are running the test and the SPID of your session, with this information the DBA will be able to find the trace on the udump directory. To get your SPID connect to the database and execute:

    select spid from v$process
    where addr=
      (select paddr from v$session
      where audsid = userenv( 'sessionid' ))

These are the steps to follow:

Connect to the database.

1) Execute:

select spid from v$process
where addr=
                   (select paddr from v$session
                    where audsid = userenv( 'sessionid' )) /

You will get your system process id, this will be part of the trace name:

SQL> select spid from v$process
  2  where addr=
  3  (select paddr from v$session
  4  where audsid = userenv( 'sessionid' ))
  5  /


2) Open a window on the user dump destination directory:

SQL> show parameters user_dump

NAME                                 TYPE    VALUE
------------------------------------ ------- ------------------------------
user_dump_dest                       string  /app01/oracle/admin/dbxprod/udump

From an OS window do:

{oracle} > cd /app01/oracle/admin/dbaprod/udump

3) From within sqlplus start tracing:

    alter session set events '10046 trace name context forever, level 12' ;

4) On the user dump destination you will get a trace named like:


{oracle} > ls -ltr total 48
-rw-r-----   1 oracle   dba         9637 Nov 22 16:26     dbxprod_ora_7713.trc

5) Execute the procedure to be checked

Exec my_proc;


Exec dbms_job.run(jobnum);

6) Exit the session when the run finish.

7) Rename the trace to a meningfull name, and send it by email:

mv dbxprod_ora_7713.trc test_my_proc_nov_22.trc

mutt -s 'trace of this process ' -a test_my_proc_nov_22.trc alejandro.vargas@oracle.com </dev/null

NOTE: Repeat the steps for every new procedure or set of steps to be introduced to production.

Join the discussion

Comments ( 1 )
  • Peter Teoh Friday, February 22, 2008
    The sql to derive the SPID don't worked (worked on 10gR2 though):
    Connected to:
    Oracle9i Enterprise Edition Release - 64bit Production
    With the Partitioning, OLAP and Oracle Data Mining options
    JServer Release - Production
    SQL> @getspid1
    (select paddr from v$session
    ERROR at line 3:
    ORA-01427: single-row subquery returns more than one row
    Any clues?
Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.