求跟踪sql 语句的方法

wuxiaobo_2009

据库是oracle9i,客户端上跑了一个企业的应用程序(无源码),想跟踪软件中某一步所执行的sql语句,该如何做?

架构是c/s/s 就是客户端(java编写），中间件(jboss/tomcat)，oracle ，请高手指教，谢谢。

xuhainanjing

试试v$session

ttboylb

下面是10046 trace的用法 请您参考：
10046 trace
To gather 10046 trace at the session level:

alter session set tracefile_identifier='10046';
alter session set timed_statistics = true;
alter session set statistics_level=all;
alter session set max_dump_file_size = unlimited;
alter session set events '10046 trace name context forever,level 12';
-- Execute the queries or operations to be traced here --
select * from dual;
exit;

If the session is not exited then the trace can be disabled using:
alter session set events '10046 trace name context off';
-----------------------------------------------------------------------------------------------------------

If trace from an existing session is required then oradebug can be used to attach to the session and initiate 10046 tracing.

The first step is to identify the session to be traced by some means:

For example, in SQL*Plus, start a session to find the OS process id (spid) for the target session:

select p.PID,p.SPID,s.SID from v$process p,v$session s where s.paddr = p.addr and s.sid = &SESSION_ID /
SPID is the operating system Process identifier (os pid)
PID is the Oracle Process identifier (ora pid)


Once the OS process id for the process has been determined then the trace can be initialised as follows:

Lets assume that the process to be traced has an os pid of 9834.

Login to SQL*Plus as a dba and execute the following:

connect / as sysdba
oradebug setospid 9834
oradebug unlimit
oradebug event 10046 trace name context forever,level 12
Remember to replace the example '9834' value with the actual os pid.

Note that it is also possible to attach to a session via oradebug using the 'setorapid'.
In this case the PID (Oracle Process identifier ) would be used (rather than the 'SPID') and the oradebug text would change to:

connect / as sysdba
oradebug setorapid 9834
oradebug unlimit
oradebug event 10046 trace name context forever,level 12

Remember to replace the example '9834' value with the actual ora pid.

To disable oradebug tracing once tracing is finished:

oradebug event 10046 trace name context off