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);
PL/SQL procedure successfully completed.
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
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.
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
。。。。省略输出。
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);
PL/SQL procedure successfully completed.
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
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.
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 ，如需转载请自行联系原作者
