/****************************
--SQLPLUS 中当前SESSION TRACE
****************************/
alter session set timed_statistics=true;
alter session set max_dump_file_size=unlimited;
alter session set tracefile_identifier='chaofeng';
alter session set events '10046 trace name context forever, level 12';
/* code to be traced goes here */
alter session set events '10046 trace name context off';
/****************************
--增强功能的alter system
****************************/
alter system set events 'sql_trace[sql: sql_id=ds9j6z3j9n49k|95hh6uvjgspm2] wait=true, bind=true, plan_stat=all_executions';
-- Trace statements with matching SQL IDs until you deactivate.
alter system set events 'sql_trace[sql: sql_id=ds9j6z3j9n49k|95hh6uvjgspm2] off';
alter system set events 'sql_trace{process:9176} wait=true, bind=true, plan_stat=all_executions';
-- Trace the identified process until you deactivate.
alter system set events 'sql_trace{process:9176} off';
alter system set events 'sql_trace{process: orapid=75} wait=true, bind=true, plan_stat=all_executions';
-- Trace the identified process until you deactivate.
alter system set events 'sql_trace{process: orapid=75} off';
alter system set events 'sql_trace{process: pname=smon|p0000|p0003} wait=true, bind=true, plan_stat=all_executions';
-- Trace the named processes until you deactivate.
alter system set events 'sql_trace{process: pname=smon|p0000|p0003} off';
alter system set events 'sql_trace[sql:sql_id=4cxhkp5ckjs6s]{process:9176}wait=true,bind=true,plan_stat=all_executions';
-- Trace the specified SQL only in the specified process until you deactivate.
alter system set events 'sql_trace[sql:sql_id=4cxhkp5ckjs6s]{process:9176}off';
/****************************
--查找trace文件位置
****************************/
--1
select value tracefile from v$diag_info where name = 'Default Trace File' ;
--2
select tracefile from v$process where addr=(
select paddr from v$session where sid=(
select sid from v$mystat where rownum=1
)
) ;
/****************************
--查看当前pending的trace
****************************/
--方法1：需要有alter system权限
$ sqlplus system
...
SQL> alter system set events 'immediate eventdump(system)';
System altered.
SQL> select value tracefile from v$diag_info where name = 'Default Trace File';
TRACEFILE
--------------------------------------------------------------------------------
/app/oracle/product/diag/rdbms/bronco/bronco/trace/bronco_ora_7461.trc
SQL> exit
$ tail /app/oracle/product/diag/rdbms/bronco/bronco/trace/bronco_ora_7461.trc
*** 2016-02-15 17:09:56.405
Dumping Event (group=SYSTEM)
sql_trace[sql: sql_id=ds9j6z3j9n49k|95hh6uvjgspm2] wait=true, bind=true, plan_stat=all_executions
sql_trace[sql: sql_id=ds9j6z3j9n49k|95hh6uvjgspm2] wait=true, bind=true, plan_stat=all_executions
--方法2：需要有DBA权限
$ sqlplus sys as sysdba
...
SQL> oradebug setmypid
Statement processed.
SQL> oradebug eventdump system
sql_trace[sql: sql_id=ds9j6z3j9n49k|95hh6uvjgspm2] wait=true, bind=true, plan_stat=all_executions
sql_trace[sql:sql_id=4cxhkp5ckjs6s]{process:9176}wait=true,bind=true,plan_stat=all_executions
SQL> alter system set events 'sql_trace[sql: sql_id=ds9j6z3j9n49k|95hh6uvjgspm2] off';
System altered.
SQL> alter system set events 'sql_trace[sql:sql_id=4cxhkp5ckjs6s]{process:9176} off';
System altered.
SQL> oradebug eventdump system
Statement processed.
/****************************
--TRACE 其他session
****************************/
-- Tell the user to connect to Oracle, but wait before executing the task you want to trace.
-- Find the user's session’s sid and serial# in v$session.
-- Tell the user to get ready to run the task, but not quite yet...
dbms_monitor.session_trace_enable(
session_id => 42,
serial_num => 1492,
waits => true,
binds => true,
plan_stat => 'all_executions'
)
-- Go! Tell the user to run her task NOW.
-- Make sure the user tells you the INSTANT the task is finished.
-- Wait until the user tells you the task is finished.
dbms_monitor.session_trace_disable(
session_id => 42,
serial_num => 1492
)
/****************************
--LOGON触发器TRACE
****************************/
create or replace trigger trace_soa after logon on database
declare
l_machine varchar2(20);
begin
select machine
into l_machine
from v$session
where sid = sys_context('USERENV','SID');
if l_machine = 'soaphq1' then
execute immediate 'alter session set timed_statistics=true';
execute immediate 'alter session set max_dump_file_size=unlimited';
execute immediate 'alter session set tracefile_identifier=chaofeng';
dbms_monitor.SESSION_TRACE_ENABLE(waits=>true);
end if;
end;
/
/*==================================*/
sys.dbms_system.set_bool_param_in_session(
:sid, :serial, 'timed_statistics', true
)
sys.dbms_system.set_int_param_in_session(
:sid, :serial,
'max_dump_file_size', 2147483647
)
/*==================================*/
--Oracle encourages you to use the DBMS_SUPPORT package instead of DBMS_SYSTEM if you have a choice (Oracle MetaLink note 62294.1).
sys.dbms_support.start_trace_in_session(
:sid, :serial,
waits=>true, binds=>false
)
/* code to be traced executes during this time window */
sys.dbms_support.stop_trace_in_session(
:sid, :serial
)
/*==================================*/
sys.dbms_system.set_ev(:sid, :serial, 10046, 8, '')
/* code to be traced executes during this time window */
sys.dbms_system.set_ev(:sid, :serial, 10046, 0, '')