Try doing it as I showed it... with the package fully qualified with the sys
schema. The "must be declared" error just means it hasn't a clue what you
are talking about -which will be the case if you are logged in as, say,
SYSTEM and then ask to run mere "dbms_system" (without explicit
qualification), since there's no package called system.dbms_system.
Regards
HJR
--
Resources for Oracle: http://www.hjrdba.com
===============================
"Mladen Gogala" <mgogala_at_earthlink.net> wrote in message
news:pan.2001.11.24.16.57.37.874.3246_at_earthlink.net...
> 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
Received on Sat Nov 24 2001 - 19:54:14 CST
