重庆分公司
重庆分公司，新征程启航
为企业提供网站建设、域名注册、服务器等服务
使用SQL_TRACE进行数据库诊断
http://www.eygle.com/case/Use.sql_trace.to.Diagnose.database.htm
为企业提供成都网站设计、成都网站制作、网站优化、成都全网营销推广、竞价托管、品牌运营等营销获客服务。创新互联建站拥有网络营销运营团队，以丰富的互联网营销经验助力企业精准获客，真正落地解决中小企业营销获客难题，做到“让获客更简单”。自创立至今，成功用技术实力解决了企业“网站建设、网络品牌塑造、网络营销”三大难题，同时降低了营销成本，提高了有效客户转化率，获得了众多企业客户的高度认可！
包dbms_system定义如下：
SQL> desc dbms_system
PROCEDURE ADD_PARAMETER_VALUE
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
PARNAME VARCHAR2 IN
VALUE VARCHAR2 IN
SCOPE VARCHAR2 IN DEFAULT
SID VARCHAR2 IN DEFAULT
POSITION BINARY_INTEGER IN DEFAULT
PROCEDURE DIST_TXN_SYNC
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
INST_NUM NUMBER IN
PROCEDURE GET_ENV
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
VAR VARCHAR2 IN
VAL VARCHAR2 OUT
PROCEDURE KCFRMS
PROCEDURE KSDDDT
PROCEDURE KSDFLS
PROCEDURE KSDIND
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
LVL BINARY_INTEGER IN
PROCEDURE KSDWRT
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
DEST BINARY_INTEGER IN
TST VARCHAR2 IN
PROCEDURE READ_EV
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
IEV BINARY_INTEGER IN
OEV BINARY_INTEGER OUT
PROCEDURE REMOVE_PARAMETER_VALUE
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
PARNAME VARCHAR2 IN
VALUE VARCHAR2 IN
SCOPE VARCHAR2 IN DEFAULT
SID VARCHAR2 IN DEFAULT
PROCEDURE REMOVE_PARAMETER_VALUE
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
PARNAME VARCHAR2 IN
POSITION BINARY_INTEGER IN
SCOPE VARCHAR2 IN DEFAULT
SID VARCHAR2 IN DEFAULT
PROCEDURE SET_BOOL_PARAM_IN_SESSION
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
SID NUMBER IN
SERIAL# NUMBER IN
PARNAM VARCHAR2 IN
BVAL BOOLEAN IN
PROCEDURE SET_EV
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
SI BINARY_INTEGER IN
SE BINARY_INTEGER IN
EV BINARY_INTEGER IN
LE BINARY_INTEGER IN
NM VARCHAR2 IN
PROCEDURE SET_INT_PARAM_IN_SESSION
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
SID NUMBER IN
SERIAL# NUMBER IN
PARNAM VARCHAR2 IN
INTVAL BINARY_INTEGER IN
PROCEDURE SET_SQL_TRACE_IN_SESSION
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
SID NUMBER IN
SERIAL# NUMBER IN
SQL_TRACE BOOLEAN IN
PROCEDURE WAIT_FOR_EVENT
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
EVENT VARCHAR2 IN
EXTENDED_ID BINARY_INTEGER IN
TIMEOUT BINARY_INTEGER IN
--开启sql_trace功能
exec dbms_system.set_sql_trace_in_session(42,1853,true);
--关闭sql_trace功能
exec dbms_system.set_sql_trace_in_session(42,1853,false);
--查询trace文件位置：
select c.value || '/' || d.instance_name || '_ora_' || a.spid || '.trc' trace
FROM v$process a, v$session b, v$parameter c, v$instance d
WHERE a.addr = b.paddr
AND b.audsid = userenv('sessionid')
AND c.name = 'user_dump_dest';
二、针对别的窗口开启10046事件，此功能可以在sql执行前开启，也可以在sql执行后开启。
--开启10046事件
exec dbms_system.set_ev(42,1853,10046,12,'');
--关闭10046事件
exec dbms_system.set_ev(42,1853,10046,0,'');
--查询trace文件位置：
select c.value || '/' || d.instance_name || '_ora_' || a.spid || '.trc' trace
FROM v$process a, v$session b, v$parameter c, v$instance d
WHERE a.addr = b.paddr
AND b.audsid = userenv('sessionid')
AND c.name = 'user_dump_dest';
网页题目：使用SQL_TRACE进行数据库诊断
标题来源：http://cqcxhl.cn/article/gsdpdp.html