在Oracle数据库中，要跟踪某个特定session执行过的SQL语句，可以使用SQL TRACE功能，然后从生成的dump文件中查看。

首先确定要跟踪的session的sid：

SQL> select distinct sid from v$mystat;

SID
----------
1083

SQL> select sid,serial# from v$session where sid=1083;

SID SERIAL#
---------- ----------
1083 221

开启跟踪功能：

SQL> execute dbms_system.SET_SQL_TRACE_IN_SESSION(1083,221,true);

PL/SQL procedure successfully completed.

为了防止生成的trace文件达到上限，可以根据情况设置：

SQL> execute dbms_system.SET_INT_PARAM_IN_SESSION(1083,221,'max_dump_file_size',2147483647);

然后1083号session就可以执行一些特定的操作了，完成后关闭跟踪：

SQL> execute dbms_system.SET_SQL_TRACE_IN_SESSION(1083,221,false);

PL/SQL procedure successfully completed.

接下来就可以到udump目录下查看生成的trace文件了，具体的文件名可以使用以下办法查找：

SQL> select sid,paddr from v$session where sid=1083;

SID PADDR
---------- ----------------
1083 0700000033373BB8

SQL> select spid from v$process where addr='0700000033373BB8';

SPID
------------
307262

SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options

p5b2@/home/oracle$ cd $ORACLE_BASE/admin/banpingsid/udump

p5b2@/u01/app/oracle/admin/banpingsid/udump$ ls -l *307262*
-rw-r----- 1 oracle oinstall 2687127 Nov 24 16:21 banping_ora_307262.trc

使用tkprof生成格式化结果：

p5b2@/u01/app/oracle/admin/banpingsid/udump$ tkprof banping_ora_307262.trc result.txt sys=no

TKPROF: Release 10.2.0.3.0 - Production on Tue Nov 24 17:04:01 2009

Copyright (c) 1982, 2005, Oracle. All rights reserved.

查看结果：

p5b2@/u01/app/oracle/admin/banpingsid/udump$ more result.txt

TKPROF: Release 10.2.0.3.0 - Production on Tue Nov 24 17:04:01 2009

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Trace file: banping_ora_307262.trc
Sort options: default

********************************************************************************
count = number of times OCI procedure was executed
cpu = cpu time in seconds executing
elapsed = elapsed time in seconds executing
disk = number of physical reads of buffers from disk
query = number of buffers gotten for consistent read
current = number of buffers gotten in current mode (usually for update)
rows = number of rows processed by the fetch or execute call
********************************************************************************

select deftext
from
defs where sysid=0 and deftype=100 and defname='|RATY|[缺省]'

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.03 0.02 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 0.00 0.00 0 2 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.03 0.02 0 2 0 0

Misses in library cache during parse: 1
Optimizer mode: CHOOSE
Parsing user id: 57

Rows Row Source Operation
------- ---------------------------------------------------
0 TABLE ACCESS BY INDEX ROWID DEFS (cr=2 pr=0 pw=0 time=29 us)
0 INDEX UNIQUE SCAN DEFS_PRIMARYKEY (cr=2 pr=0 pw=0 time=25 us)(object id 56359)

********************************************************************************

......

该session所有的SQL信息都已经很详细的被记录下来了。

2009/11/27 00:11 - Oracle