Re: Cannot Set Trace

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: 24 Nov 2001 14:38:31 -0800
Message-ID: <9tp7h7013cp@drn.newsguy.com>

In article <pan.2001.11.24.16.57.37.874.3246_at_earthlink.net>, "Mladen says...
>
>In article <3c00097c$0$28128$afc38c87_at_news.optusnet.com.au>, "Howard J.
>Rogers" <dba_at_hjrdba.com> wrote:
>
>> It certainly does work in 8.1.7. As proof, I offer:
>>
>> SVRMGR> select * from v$version;
>> BANNER
>> ---------------------------------------------------------------- Oracle8i
>> Enterprise Edition Release 8.1.7.0.0 - Production PL/SQL Release
>> 8.1.7.0.0 - Production CORE 8.1.7.0.0 Production TNS for 32-bit
>> Windows: Version 8.1.7.0.0 - Production NLSRTL Version 3.4.1.0.0 -
>> Production 5 rows selected.
>>
>> SVRMGR> select sid, serial#, username from v$session; SID SERIAL#
>> USERNAME
>> ---------- ---------- ------------------------------
>> 1 1
>> 2 1
>> 3 1
>> 4 1
>> 5 1
>> 6 1
>> 7 1
>> 8 18 DBSNMP
>> 9 206 SYS
>> 11 1198 SCOTT
>> 10 rows selected.
>>
>> SVRMGR> execute sys.dbms_system.set_sql_trace_in_session(11,1198,TRUE)
>> Statement processed.
>>
>> ...And that session then did start producing trace files, in the
>> directory the user_dump_dest parameter was pointing to.
>>
>> What makes you say it doesn't work?
>
>This:
>
>$ sqlplus /
>
>SQL*Plus: Release 8.1.7.0.0 - Production on Sat Nov 24 16:36:47 2001
>
>(c) Copyright 2000 Oracle Corporation. All rights reserved.
>
>
>Connected to:
>Oracle8i Enterprise Edition Release 8.1.7.2.0 - Production With the
>Partitioning option
>JServer Release 8.1.7.2.0 - Production
>
>SQL> exec dbms_system.set_sql_trace_in_session(9,4,TRUE); BEGIN
>dbms_system.set_sql_trace_in_session(9,4,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
>
>

that just means you didn't execute SYS.dbms_system.set_sql_trace_in_session

this package does not have a public synonym defined for it.

As HJR says -- it surely does work...

ops$tkyte_at_ORA817DEV.US.ORACLE.COM> select c.value || '/' || d.instance_name || '_ora_' || to_char(a.spid,'fm99999') || '.trc' 2 from v$process a, v$session b, v$parameter c, v$instance d

  3   where a.addr = b.paddr
  4     and b.audsid = userenv('sessionid')
  5     and c.name = 'user_dump_dest'

6 /

C.VALUE||'/'||D.INSTANCE_NAME||'_ORA_'||TO_CHAR(A.SPID,'FM99999')||'.TRC'

/export/home/ora817/admin/ora817dev/udump/ora817dev_ora_16757.trc

ops$tkyte_at_ORA817DEV.US.ORACLE.COM> !ls -l /export/home/ora817/admin/ora817dev/udump/ora817dev_ora_16757.trc; /export/home/ora817/admin/ora817dev/udump/ora817dev_ora_16757.trc not found

See, no trace file yet....

ops$tkyte_at_ORA817DEV.US.ORACLE.COM> select sid, serial# from v$session 2 where sid = ( select sid from v$mystat where rownum = 1 );

SID SERIAL#
---------- ----------

18 69

ops$tkyte_at_ORA817DEV.US.ORACLE.COM> exec
SYS.dbms_system.set_sql_trace_in_session(18,69,TRUE);

PL/SQL procedure successfully completed.

ops$tkyte_at_ORA817DEV.US.ORACLE.COM> !ls
/export/home/ora817/admin/ora817dev/udump/ora817dev_ora_16757.trc /export/home/ora817/admin/ora817dev/udump/ora817dev_ora_16757.trc

and now there is and its a sql trace tracefile:

ops$tkyte_at_ORA817DEV.US.ORACLE.COM> !cat
/export/home/ora817/admin/ora817dev/udump/ora817dev_ora_16757.trc Dump file /export/home/ora817/admin/ora817dev/udump/ora817dev_ora_16757.trc Oracle8i Enterprise Edition Release 8.1.7.2.0 - Production With the Partitioning option
JServer Release 8.1.7.2.0 - Production
ORACLE_HOME = /export/home/ora817
System name: SunOS

Node name:      aria-dev
Release:        5.7
Version:        Generic_106541-08
Machine:        sun4u

Instance name: ora817dev
Redo thread mounted by this instance: 1
Oracle process number: 12
Unix process pid: 16757, image: oracle_at_aria-dev (TNS V1-V3)

2001-11-24 17:33:40.197
SESSION ID:(18.69) 2001-11-24 17:33:40.164 APPNAME mod='SQL*Plus' mh=3669949024 act='' ah=4029777240
PARSING IN CURSOR #2 len=52 dep=0 uid=63 oct=47 lid=63 tim=1879874211 hv=1697159799 ad='86be9c08' BEGIN DBMS_OUTPUT.GET_LINES(:LINES, :NUMLINES); END; END OF STMT PARSE #2:c=0,e=3,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=0,tim=1879874211 EXEC #2:c=0,e=1,p=0,cr=0,cu=0,mis=0,r=1,dep=0,og=4,tim=1879874212

So, just make sure you

have the ability to execute dbms_system
fully qualify the package name with the owning schema.

>SQL>
>
>There is a file called prvtutil.plb on $ORACLE_HOME/rdbms/admin, but even
>if I execute this file, "SET_SQL_TRACE_IN_SESSION" does not produce any
>files. No sweat, I found several ways of replacing the missing utility. One
>is a database trigeer ("AFTER EVENT" in DBA_TRIGERS) and another one is
>oradebug and setting the required event (10046, level 4) but that would be
>rather hard to swallow by tkprof.

why? tkprof shouldn't have any problems with that.

> Oradebug magic has a problem because it
>can only be done if you are connected as SYSDBA and for security reasons,
>it is allowed only from the server, so I loose all the nifty windows
>features. Database triggers are not that bad either. and they catch ALL the
>statements, not just from one cutoff point on.
>
>
>--
>Mladen Gogala

--
Thomas Kyte (tkyte@us.oracle.com)             http://asktom.oracle.com/ 
Expert one on one Oracle, programming techniques and solutions for Oracle.
http://www.amazon.com/exec/obidos/ASIN/1861004826/  
Opinions are mine and do not necessarily reflect those of Oracle Corp

Received on Sat Nov 24 2001 - 16:38:31 CST