SYS@SOURCE10 > SELECT * FROM v$version WHERE ROWNUM=1;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Prod
[oracle10@WESTZQ ~]$ which trcsess
/u01/oracle10/app/oracle/product/10.2.0/db_1/bin/trcsess
[oracle10@WESTZQ ~]$ trcsess
oracle.ss.tools.trcsess.SessTrcException: SessTrc-00002: Session Trace Usage error: Wrong parameters passed.
trcsess [output=] [session=] [clientid=] [service=] [action=] [module=]
output= output destination default being standard output.
session= session to be traced.
Session id is a combination of session Index & session serial number e.g. 8.13.
clientid= clientid to be traced.
service= service to be traced.
action= action to be traced.
module= module to be traced.
Space separated list of trace files with wild card '*' supported.
可以看到,trcsess其实是个JAVA程序
可以跟踪:
session id:为sid.serial#这种格式
client id:对应v$session.client_identifier,由dbms_session.set_identifier设置
service name:对应v$session.SERVICE_NAME,SYS$BACKGROUND / SYS$USERS 用于区分前后台进程
action name:对应v$session.ACTION,由DBMS_APPLICATION_INFO.SET_ACTION 设置
module name:对应v$session.MODULE,由DBMS_APPLICATION_INFO.SET_MODULE 设置
[oracle10@WESTZQ udump]$ date
Sun Jun 21 14:21:17 CST 2009
开了4个会话运行如下SQL
---------------------------------------------------------------------------------
Alter session set events '10046 trace name context forever,level 12';
BEGIN
dbms_session.set_identifier('zhangqiaoc_test');
END;
/
<< SQL1 >>
BEGIN
dbms_session.set_identifier(NULL);
END;
/
<< SQL2 >>
Alter session set events '10046 trace name context off';
---------------------------------------------------------------------------------
[oracle10@WESTZQ udump]$ trcsess clientid=zhangqiaoc_test * > /tmp/trcsess.out
[oracle10@WESTZQ udump]$ cat /tmp/trcsess.out|grep -i select
select 'x' from dual
SELECT * FROM test WHERE object_name='I_OBJ#'
SELECT * FROM test
select 'x' from dual
SELECT * FROM test WHERE data_object_id=44
SELECT * FROM test
select o.owner#,o.name,o.namespace,o.remoteowner,o.linkname,o.subname,o.dataobj#,o.flags from obj$ o where o.obj#=:1
SELECT COUNT(*) FROM test
SELECT * FROM test
SELECT COUNT(*) FROM test
SELECT * FROM test
经过验证,不存在前面中<< SQL2 >>中执行过的SQL语句
ORACLE是如何把同个client id的语句提取出来的了?
在运行了dbms_session.set_identifier后,会自动在trc文件中加上一行:
*** CLIENT ID:(zhangqiaoc_test) 2009-06-21 14:27:23.214
*** CLIENT ID:() 2009-06-21 14:27:23.259
这样,中间的就是client id=zhangqiaoc_test的SQL语句
trcsess生成的还是个裸的trc文件,仍然需要使用tkprof转下
[oracle10@WESTZQ tmp]$ tkprof trcsess.out tkprof.out
使用trcsess,加上dbms_session.set_identifier,可以进行共享服务器模式下端到端的跟踪,或者BS模式下端到端的跟踪
但是前提是应用上集成进了dbms_session.set_identifier,以设置client_identifier
通过变现一个func可以轻松实现设置:
CREATE OR REPLACE FUNCTION SET_IDENTIFIER(AVC_IDENTIFIER VARCHAR2)
RETURN NUMBER AS
BEGIN
DBMS_SESSION.SET_IDENTIFIER(AVC_IDENTIFIER);
RETURN 100;
END;
---------------------------------------------------------------
刚才研究了下,用dbms_monitor.client_id_trace_disable('identified_id')开启追踪就好了
但是在应用服务器中怎么设置这个还是个问题
跟踪的视图
SELECT * FROM dba_enabled_traces;
SELECT * FROM dba_enabled_aggregations;
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/8242091/viewspace-607222/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/8242091/viewspace-607222/