Using Sql_trace to track the operation of a specified session

1. SYS user authorizes the administrative user.
Sql> Grant execute on Sys.dbms_system to Andy;


Grant succeeded.


2. Query the sid,serial# of the tracked user
Sql> Select sid,serial# from v$session where username= ' DBLINK ';


SID serial#
---------- ----------
31 7
3. Manage users Andy starts tracking Dblink users
sql> exec sys.dbms_system.set_sql_trace_in_session (31,7,true);


PL/SQL procedure successfully completed.


4. Dblink is followed by the user executing the test statement.
Sql> select * from User_tables;


5. End of Trace
sql> exec sys.dbms_system.set_sql_trace_in_session (31,7,false);


PL/SQL procedure successfully completed.


6. Dblink users to query their current trace file number
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. Create a view file with the Tkprof tool
[Email protected] ~]$ 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, the Oracle and/or its affiliates. All rights reserved.


8. View Tracking Records
[email protected] ~]$ cat Andy2.txt


Tkprof:release 11.2.0.1.0-development on Mon Nov 17 16:48:38 2014


Copyright (c) 1982, the 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
。。。。 Omit the output.



OK, end. Reprint please indicate the source

Using Sql_trace to track the operation of a specified session