identifier 'DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION' must be declared

 

由“如何跟踪某个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.eygle.com/case/Use.sql_trace.to.Diagnose.database.htm

http://www.banping.com/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