由“如何跟踪某个session的SQL”来试用sql_trace进行sql跟踪,
18:43:27 ejing@ORCL> select distinct sid from v$mystat; SID ---------- 133
18:45:06 ejing@ORCL> select sid,serial# from v$session where sid=133; SID SERIAL# ---------- ---------- 133 26
windows环境下报错: 18:47:01 ejing@ORCL> execute dbms_system.set_sql_trace_in_session(133,26,true); BEGIN dbms_system.set_sql_trace_in_session(133,26,true); END; * ERROR at line 1: ORA-06550: line 1, column 7: PLS-00201: identifier 'DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION' must be declared ORA-06550: line 1, column 7: PL/SQL: Statement ignored 网上查找到解决方法,
hi, when i ran the execute dbms_system.set_sql_trace_in_session(78,25435,true); then the following error is occuring. Pls let me know how to get rid of this error? PLS-00201: identifier 'DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION' must be declared thanks Parijat
julian 03-12-2002, 07:06 AM Run as SYS prvtutil.plb script. You will find it in $ORACLE_HOME/rdbms/admin/ Then, still as SYS: SVRMGR> GRANT EXECUTE ON DBMS_SYSTEM TO USER_NAME; Then, as USER_NAME, create a synonym: SVRMGR> CREATE SYNONYM DBMS_SYSTEM FOR SYS.DBMS_SYSTEM;
于是:
19:02:21 sys@ORCL> @"D:\app\fly2bleusky\product\11.1.0\db_1\RDBMS\ADMIN\prvtutil.plb" View created. Type created. Type created. Package body created. Package created. Package body created.
19:12:12 sys@ORCL> grant execute on dbms_system to ejing; Grant succeeded.
19:13:02 ejing@ORCL> create synonym dbms_system for sys.dbms_system; Synonym created. 19:13:24 ejing@ORCL> exec dbms_system.set_sql_trace_in_session(133,26,true); PL/SQL procedure successfully completed.
接下来继续: http://www./case/Use.sql_trace.to.Diagnose.database.htm http://www./2009/11/27/sqltrace_session/
19:26:03 ejing@ORCL> exec dbms_system.set_sql_trace_in_session(133,32,true); PL/SQL procedure successfully completed. 19:26:11 ejing@ORCL> exec dbms_system.set_sql_trace_in_session(133,32,false); PL/SQL procedure successfully completed.
19:27:51 ejing@ORCL> select sid,paddr from v$session where sid=133; SID PADDR ---------- -------- 133 2FA92D00 19:30:04 ejing@ORCL> select spid from v$process where addr='2FA92D00'; SPID ------------------------ 4760 trace file@D:\app\fly2bleusky\diag\rdbms\orcl\orcl\trace orcl_ora_4760.trc 用UltraEditor打开,从头到尾浏览了一下,看懂一点点,
===================== PARSING IN CURSOR #3 len=22 dep=0 uid=88 oct=3 lid=88 tim=10816393517 hv=1993244958 ad='2813ae5c' sqlid='68bgnf5vcx08y' select count(*) from a END OF STMT PARSE #3:c=15600,e=28617,p=7,cr=26,cu=0,mis=1,r=0,dep=0,og=1,tim=10816393510 EXEC #3:c=0,e=65,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=10816393793 FETCH #3:c=0,e=92,p=0,cr=7,cu=0,mis=0,r=1,dep=0,og=1,tim=10816393996 STAT #3 id=1 cnt=1 pid=0 pos=1 obj=0 op='SORT AGGREGATE (cr=7 pr=0 pw=0 time=0 us)' STAT #3 id=2 cnt=6 pid=1 pos=1 obj=72098 op='TABLE ACCESS FULL A (cr=7 pr=0 pw=0 time=3 us cost=3 size=0 card=6)' FETCH #3:c=0,e=4,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,tim=10816394642 ===================== PARSING IN CURSOR #5 len=52 dep=0 uid=88 oct=47 lid=88 tim=10816398138 hv=1029988163 ad='298fef98' sqlid='9babjv8yq8ru3' BEGIN DBMS_OUTPUT.GET_LINES(:LINES, :NUMLINES); END; END OF STMT PARSE #5:c=0,e=52,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=10816398130 EXEC #5:c=0,e=361,p=0,cr=0,cu=0,mis=0,r=1,dep=0,og=1,tim=10816398817
*** 2011-05-26 19:23:39.643 ===================== PARSING IN CURSOR #3 len=33 dep=0 uid=88 oct=42 lid=88 tim=10830604536 hv=0 ad='cd0fc04' sqlid='0000000000000' alter session set sql_trace=false END OF STMT PARSE #3:c=0,e=807,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,tim=10830604528 EXEC #3:c=0,e=73,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=10830605020
*** 2011-05-26 19:26:11.388 ===================== PARSING IN CURSOR #5 len=52 dep=0 uid=88 oct=47 lid=88 tim=10982349561 hv=1029988163 ad='298fef98' sqlid='9babjv8yq8ru3' BEGIN DBMS_OUTPUT.GET_LINES(:LINES, :NUMLINES); END; END OF STMT PARSE #5:c=0,e=77,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=10982349551 EXEC #5:c=0,e=423,p=0,cr=0,cu=0,mis=0,r=1,dep=0,og=1,tim=10982350510
*** 2011-05-26 19:27:04.599 ===================== PARSING IN CURSOR #3 len=64 dep=0 uid=88 oct=47 lid=88 tim=11035561858 hv=831792803 ad='280c8260' sqlid='c9w0ws8st89p3' BEGIN dbms_system.set_sql_trace_in_session(133,32,false); END; END OF STMT PARSE #3:c=0,e=31334,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,tim=11035561850 EXEC #3:c=0,e=139,p=0,cr=0,cu=0,mis=0,r=1,dep=0,og=1,tim=11035562508
|
|
来自: wghbeyond > 《oracle故障处理》