Alejandro Vargas' Blog
- December 5, 2006
How to Setup Tracing for Code Check
- 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 /
SPID
---------
7713
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:
DatabaseName_ora_processID.trc
{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;
Or
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.
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production
SQL> @getspid1
(select paddr from v$session
*
ERROR at line 3:
ORA-01427: single-row subquery returns more than one row
Any clues?