Page tree
Skip to end of metadata
Go to start of metadata
/****************************
--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, '')
  • No labels