how to start '10046 trace name context forever,level 12'
How to use alter session set events 'immediate trace name systemstate level 10';
1.Session Tracing
alter session set events '10046 trace name context forever,level 12';
alter session set events '10046 trace name context off';
e.g.
C:\Documents and Settings\zhanglei>sqlplus /nolog
SQL*Plus: Release 10.2.0.1.0 - Production on 星期五 4月 17 13:57:56 2009
Copyright (c) 1982, 2005, Oracle. All rights reserved.
SQL> conn ecc_view/ecc@devdb
已連線。
SQL> alter session set events '10046 trace name context forever,level 12';
會話已更改。
SQL> select * from dual;
D
-
X
[oracle@rac1 udump]$ ll -t
total 340
-rw-r----- 1 oracle oinstall 2360 Apr 17 13:57 devdb1_ora_5509.trc
SQL> alter session set events '10046 trace name context off';
2.Tracing a process after it has started
A.start a session to find the OS process id (spid) for the target session
select p.PID,p.SPID,s.SID
from v$process p,v$session s
where s.paddr = p.addr
and s.USERNAME = 'ECC_VIEW'
PID SPID SID
--- ---- ---
28 10878 129
B.trace can be initialised
1)use spid to trace
connect / as sysdba
oradebug setospid "&spid"
oradebug unlimit
oradebug event 10046 trace name context forever,level 12
oradebug event 10046 trace name context off
e.g.
SQL> conn / as sysdba
Connected.
SQL> oradebug setospid 10878
Oracle pid: 28, Unix process pid: 10878, image:
SQL> oradebug unlimit
Statement processed.
SQL> oradebug event 10046 trace name context forever,level 12
Statement processed.
[oracle@rac1 udump]$ ll -t
total 352
-rw-r----- 1 oracle oinstall 987 Apr 17 14:12 devdb1_ora_10878.trc
2)use pid to trace
connect / as sysdba
oradebug setorapid "&pid"
oradebug unlimit
oradebug event 10046 trace name context forever,level 12
oradebug event 10046 trace name context off
e.g.
SQL> conn / as sysdba
Connected.
SQL> oradebug setorapid 28
Unix process pid: 10878, image:
SQL> oradebug unlimit
Statement processed.
SQL> oradebug event 10046 trace name context forever,level 12
Statement processed.
[oracle@rac1 udump]$ ll -t
total 360
-rw-r----- 1 oracle oinstall 1369 Apr 17 14:17 devdb1_ora_10878.trc
To disable oradebug tracing once tracing is finished:
SQL> oradebug event 10046 trace name context off
3)using DBMS_SUPPORT package to trace session
exec DBMS_SUPPORT.START_TRACE_IN_SESSION( &SID, waits=>true, binds=>true )
exec DBMS_SUPPORT.STOP_TRACE_IN_SESSION( &SID , null )
e.g.
(Doc ID: 377204.1 )
SQL> conn / as sysdba
SQL> @/u01/app/oracle/product/10.2.0/db_1/rdbms/admin/dbmssupp.sql
Package created.
Package body created.
SQL> grant execute on dbms_support to ecc_view;
Grant succeeded.
SQL> create public synonym dbms_support for dbms_support;
Synonym created.
SQL> exec dbms_support.start_trace_in_session(sid => 129,serial => 2144,waits => true,binds => true);
PL/SQL procedure successfully completed
[oracle@rac1 udump]$ ll -t
total 364
-rw-r----- 1 oracle oinstall 7857 Apr 17 14:42 devdb1_ora_10878.trc
SQL> exec dbms_support.stop_trace_in_session(sid => 129, serial => 2144);
PL/SQL procedure successfully completed
3.Instance wide tracing
alter system set events '10046 trace name context forever,level 12';
alter system set events '10046 trace name context off';
SQL> alter system set events '10046 trace name context forever,level 12';
System altered
SQL> conn ecc_view/ecc@devdb
已連線。
SQL> select sysdate from dual;
SYSDATE
--------------
17-4月 -09
SQL> conn / as sysdba
select p.PID,p.SPID,s.SID,s.SERIAL#
from v$process p,v$session s
where s.paddr = p.addr
and s.USERNAME = 'ECC_VIEW'
PID SPID SID s.SERIAL#
--- ---- --- ---------
28 29109 129 2146
[oracle@rac1 udump]$ ll -t
total 392
-rw-r----- 1 oracle oinstall 22918 Apr 17 14:57 devdb1_ora_29109.trc
4.Initialisation parameter setting
This setting will trace every session in the instance when it is restarted.
event="10046 trace name context forever,level 12"
The setting can be disabled by removing the parameter and restarting the instance or by using an alter system command as
alter system set events '10046 trace name context off';
5.Via a Logon Trigger
CREATE OR REPLACE TRIGGER SYS.set_trace
AFTER LOGON ON DATABASE
WHEN (USER like '&USERNAME')
DECLARE
lcommand varchar(200);
BEGIN
EXECUTE IMMEDIATE 'alter session set statistics_level=ALL';
EXECUTE IMMEDIATE 'alter session set max_dump_file_size=UNLIMITED';
EXECUTE IMMEDIATE 'alter session set events ''10046 trace name context forever, level 12''';
END set_trace;
/
e.g.
CREATE OR REPLACE TRIGGER SYS.set_trace
AFTER LOGON ON DATABASE
WHEN (USER = 'ECC_VIEW')
DECLARE
lcommand varchar(200);
BEGIN
EXECUTE IMMEDIATE 'alter session set statistics_level=ALL';
EXECUTE IMMEDIATE 'alter session set max_dump_file_size=UNLIMITED';
EXECUTE IMMEDIATE 'alter session set events ''10046 trace name context forever, level 12''';
END set_trace;
select p.PID,p.SPID,s.SID,s.SERIAL#
from v$process p,v$session s
where s.paddr = p.addr
and s.USERNAME = 'ECC_VIEW'
[oracle@rac1 udump]$ ll -t
total 432
-rw-r----- 1 oracle oinstall 10573 Apr 17 15:26 devdb1_ora_8328.trc
Subject: Recommended Method for Obtaining 10046 trace for Tuning
Doc ID: 376442.1 Type: FAQ
Modified Date : 15-APR-2009 Status: PUBLISHED
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/9252210/viewspace-590818/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- Oracle 10046 SQL TRACEOracleSQL
- 單個SQL語句的10046 traceSQL
- ADFS – How to enable Trace Debugging and advanced access logging
- 【TRACE】如果通過10046跟蹤資料庫效能問題資料庫
- 從10046 trace 的trca報告中總結的時間模型示例模型
- [原創] How to revise author name and email in commit historyAIMIT
- [20190522]How to get dump or list parameters set at session level.txtSession
- [20200818]12c 10046跟蹤時間戳.txt時間戳
- [20200818]12c 10046跟蹤時間戳2.txt時間戳
- 10046事件概述事件
- Forever +nginx部署Node站點Nginx
- 10046 tracefile註釋
- 《Lua-in-ConTeXt》12:zhfonts 模組備忘錄Context
- PG12-2 B-Tree 索引 level 0 root頁索引
- PG 12-2 B-Tree 索引 分析 分裂 level = 1索引
- [20181103]12c檢視V$EVENT_NAME.txt
- iron man forever:以凡人之軀,比肩神明
- 【廣西省賽#6】F.Forever_Young
- SQL TraceSQL
- [20181116]SYS_CONTEXT函式的使用(12c).txtContext函式
- Go:context.ContextGoContext
- 從10046看Oracle分割槽裁剪Oracle
- Oracle 12C ORA-12545 While Connecting to RAC through SCAN NameOracleWhile
- pg12中oid2name新增選項介紹
- Oracle session traceOracleSession
- Level Up
- start uniappAPP
- ContextContext
- go 上下文:context.ContextGoContext
- DBMS_TRACE(zt)
- How to ssh
- 用10046進行診斷一例
- 記一次kafka的high level和low levelKafka
- 《Lua-in-ConTeXt》02:ConTeXt 計算機Context計算機
- WPF筆記3——x:Name 與 Name筆記
- 聊聊ContextContext
- 理解ContextContext
- Go ContextGoContext
- context包Context