利用sql_trace跟蹤一個指定會話的操作

1.  sys使用者給管理使用者授權。

SQL> grant execute on sys.dbms_system to andy;

Grant succeeded.

2. 查詢被跟蹤使用者的sid,serial#  

SQL> select sid,serial# from v$session where username='DBLINK';

       SID    SERIAL#

---------- ----------

31    7

3.  管理使用者andy開始跟蹤dblink使用者

SQL> exec sys.dbms_system.set_sql_trace_in_session(31,7,true);

PL/SQL procedure successfully completed.

4.  dblink被跟蹤使用者執行測試語句。

SQL> select * from user_tables;

5.  跟蹤結束

SQL> exec sys.dbms_system.set_sql_trace_in_session(31,7,false);

6.  dblink使用者查詢自己目前trace檔案号

SQL>  select * from v$diag_info where name like 'Default%';

   INST_ID NAME

---------- ----------------------------------------------------------------

VALUE

--------------------------------------------------------------------------------

1 Default Trace File

/home/oracle/app/diag/rdbms/orcl/orcl/trace/orcl_ora_3524.trc

7. 用tkprof工具生成檢視檔案

[oracle@11g ~]$ tkprof /home/oracle/app/diag/rdbms/orcl/orcl/trace/orcl_ora_3524.trc andy2.txt sys=no

TKPROF: Release 11.2.0.1.0 - Development on Mon Nov 17 16:48:38 2014

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

8.  檢視跟蹤記錄

[oracle@11g ~]$ cat andy2.txt

Trace file: /home/oracle/app/diag/rdbms/orcl/orcl/trace/orcl_ora_3524.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

SQL ID: dtbhjabjx3v1u

Plan Hash: 4102440123

select * 

from                       <--  #剛執行sql找到

 user_tables           

call     count       cpu    elapsed       disk      query    current        rows

------- ------  -------- ---------- ---------- ---------- ----------  ----------

Parse        1      0.09       0.10          0          0          0           0

Execute      1      0.00       0.00          0          0          0           0

Fetch        2      0.11       0.31        174       1390          0           1

total        4      0.21       0.41        174       1390          0           1

。。。。省略輸出。

本文轉自 張沖andy 部落格園部落格，原文連結：http://www.cnblogs.com/andy6/p/6195942.html   ，如需轉載請自行聯系原作者