在SYS使用者下，跟蹤CHEN使用者

===
SYS
===
SQL> select sid,serial#,username from v$session where username is not null;

SID SERIAL# USERNAME
---------- ---------- ------------------------------
124 2264 CHEN
。。。。。。

12 rows selected.

開啟CHEN使用者 跟蹤
SQL> exec dbms_system.set_sql_trace_in_session(124,2264,true);

====
CHEN
====
SQL> show user
USER is "CHEN"
SQL> select * from tab;
SQL> select ename,deptno,sal,rank() over(partition by deptno order by sal desc) from emp;

SQL> select deptno,sum(sal),max(sal),min(sal) from emp group by deptno;

===
SYS
===

SQL> show user
USER is "SYS"

關閉CHEN使用者 跟蹤
SQL> exec dbms_system.set_sql_trace_in_session(124,2264,false);
檢視跟蹤檔案位置
SQL> show parameter user_dump
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
user_dump_dest string /u2/app/oracle/diag/rdbms/orcl
/orcl/trace

[oracle11@localhost ~]$ cd /u2/app/oracle/diag/rdbms/orcl/orcl/trace/

格式跟蹤檔案

[oracle11@localhost trace]$ tkprof orcl_ora_22891.trc chen0626.trc

TKPROF: Release 11.2.0.1.0 - Development on Fri Jun 26 10:25:07 2015
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

檢視CHEN使用者跟蹤 檔案

[oracle11@localhost trace]$ vim chen0626.trc

TKPROF: Release 11.2.0.1.0 - Development on Fri Jun 26 10:25:07 2015
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

Trace file: orcl_ora_22891.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: cxfjxyu06hwcy
Plan Hash: 3145491563
select ename,deptno,sal,rank() over(partition by deptno order by sal desc)
from
emp

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

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 112

Rows Row Source Operation
------- ---------------------------------------------------
14 WINDOW SORT (cr=3 pr=0 pw=0 time=0 us cost=4 size=182 card=14) 14 TABLE ACCESS FULL EMP (cr=3 pr=0 pw=0 time=13 us cost=3 size=182 card=14)
********************************************************************************
SQL ID: 5strk8bvth25r
Plan Hash: 4067220884
select deptno,sum(sal),max(sal),min(sal)
from
emp group by deptno

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

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 112

Rows Row Source Operation
------- ---------------------------------------------------
3 HASH GROUP BY (cr=3 pr=0 pw=0 time=0 us cost=4 size=21 card=3) 14 TABLE ACCESS FULL EMP (cr=3 pr=0 pw=0 time=26 us cost=3 size=98 card=14)

。。。。。。

歡迎關注我的微信公眾號"IT小Chen"，共同學習，共同成長！！！

oracle 跟蹤其他使用者

oracle 跟蹤其他使用者

相關文章