Remote session tracing problem

From: Milen Kulev <makulev_at_gmx.net>
Date: Fri, 17 Sep 2004 17:27:06 +0200 (MEST)
Message-ID: <27136.1095434826@www43.gmx.net>

HI list,
I am trying to create a PL/SQL wrapper procedure of dbms_system.set_ev that should activate tracing in another sesison. The idea is to grant execute on this procedure to specific users, so that they can trace their own sessions.

Here ist the simplified version of
the procedure (omitting some checks):

---

create or replace procedure traceon
  ( p_sid number default 0, p_serial number default 0 ) as
 v_trace_file varchar2(400);
begin

        select c.value || '/' || d.value || '_ora_' || ltrim(to_char(a.spid,'fm99999')) || '.trc' into v_trace_file   from v$process a, v$session b, v$parameter c, v$parameter d   where a.addr = b.paddr

   and c.name = 'user_dump_dest'
   and d.name ='instance_name'
   and b.sid= p_sid;
	dbms_output.put_line('TRACE_FILE = '|| v_trace_file);
	sys.dbms_system.set_ev(p_sid , p_sid , 10046, 12, '');


	-- dbms_support doesn't work too

	-- sys.dbms_support.start_trace_in_session(p_sid , p_sid, TRUE, TRUE);

end;

---

I have the following problems (all with 9.2.0.4): 1) When I execute (as Oracle User SYS ot TEST (test user)) the procedure on Solaris, it does NOTHING.
No file in (USERDUMP directory).
2) When I try to execute the procedure on HPUX 11 as SYS User , it works, but when the user TEST tries to trace another TEST user session, it does nothing.

LOGON TRIGGER is not an option in this environment.

What I am missing?

Best Regards

-- 
NEU: GMX ProMail mit bestem Virenschutz http://www.gmx.net/de/go/mail
+++ Empfehlung der Redaktion +++ Internet Professionell 10/04 +++

--
http://www.freelists.org/webpage/oracle-l

Received on Fri Sep 17 2004 - 10:23:13 CDT