使用sqltrace跟踪session执行的sql

1. 找出sid和serial#

select sid,serial#,osuser from v$session t

where t.USERNAME='USERNAME'

2. 开始sqltrace

execute dbms_system.set_sql_trace_in_session(sid,serial#,ture);

3. 运行程序

4. 停止sqltrace

execute dbms_system.set_sql_trace_in_session(sid,serial#,false);

5. 使用cmd 到udump

例: D:\Oracle\product\10.1.0\admin\test10g\udump

6. 找最新的trace文件

7. 使用tkprof文件解析trace文件

例: tkprof sqltrace文件.trc  a.out

8. 打开a.out

 

Microsoft Windows XP [版本 5.1.2600]
(C) 版权所有 1985-2001 Microsoft Corp.

C:\Documents and Settings\Administrator>sqlplus /nolog

SQL*Plus: Release 10.1.0.2.0 - Production on 星期四 11月 5 12:36:48 2009

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

SQL> connect sys/test
ERROR:
ORA-28009: connection to sys should be as sysdba or sysoper

SQL> connect sys/test as sysdba;
已连接。
SQL> show sga;

Total System Global Area  171966464 bytes
Fixed Size                   787988 bytes
Variable Size             145488364 bytes
Database Buffers           25165824 bytes
Redo Buffers                 524288 bytes
SQL> select sid,serial#,username
  2  from v$session;

       SID    SERIAL# USERNAME
---------- ---------- ------------------------------
       125        822 SYS
       126       2063 SYS
       128       1437
       130         52 DBSNMP
       131         14
       132          1 SYSMAN
       133       2575 SYS
       134         69 DBSNMP
       135        511 SYSMAN
       136        374
       137         12 SYSMAN

       SID    SERIAL# USERNAME
---------- ---------- ------------------------------
       138       4416 SYS
       139          1 SYSMAN
       140          6 SYSMAN
       141          2 SYSMAN
       142          3 SYSMAN
       143          3 SYSMAN
       144          1 SYSMAN
       147         11 SYSMAN
       148          1 SYSMAN
       152          1
       153          1

       SID    SERIAL# USERNAME
---------- ---------- ------------------------------
       154          3
       155        511
       158          1
       159          1
       160          1
       161       1879 SYS
       162          3 SYS
       163          1
       164          1
       165          1
       166          1

       SID    SERIAL# USERNAME
---------- ---------- ------------------------------
       167          1
       168          1
       169          1
       170          1

已选择37行。

SQL> /

       SID    SERIAL# USERNAME
---------- ---------- ------------------------------
       126       2065 DUREX
       127       1336 DUREX
       128       1437
       130         52 DBSNMP
       131         14
       132          1 SYSMAN
       133       2575 SYS
       134         69 DBSNMP
       135        511 SYSMAN
       136        374
       137         12 SYSMAN

       SID    SERIAL# USERNAME
---------- ---------- ------------------------------
       138       4416 SYS
       139          1 SYSMAN
       140          6 SYSMAN
       141          2 SYSMAN
       142          3 SYSMAN
       143          3 SYSMAN
       144          1 SYSMAN
       147         11 SYSMAN
       148          1 SYSMAN
       152          1
       153          1

       SID    SERIAL# USERNAME
---------- ---------- ------------------------------
       154          3
       155        511
       158          1
       159          1
       160          1
       161       1879 SYS
       162          3 SYS
       163          1
       164          1
       165          1
       166          1

       SID    SERIAL# USERNAME
---------- ---------- ------------------------------
       167          1
       168          1
       169          1
       170          1

已选择37行。

SQL> exec dbms_system.set_sql_trace_in_session(126,2065,true)

PL/SQL 过程已成功完成。

SQL> exec dbms_system.set_sql_trace_in_session(126,2065,false)

PL/SQL 过程已成功完成。

SQL>

 

C:\Documents and Settings\Administrator>d:

D:\>cd D:\oracle\product\10.1.0\admin\myoracle\udump

D:\oracle\product\10.1.0\admin\myoracle\udump>tkprof myoracle_ora_4468.trc a.out

TKPROF: Release 10.1.0.2.0 - Production on 星期四 11月 5 15:41:47 2009

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

 

D:\oracle\product\10.1.0\admin\myoracle\udump>