Re: Cannot Set Trace

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

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. 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