dbms_system.set_ev

I run
EXECUTE SYS.dbms_system.set_ev (42, 45529,10046,12,'');
or
EXECUTE SYS.dbms_system.set_sql_trace_in_session (42, 45529, TRUE);

PL/SQL procedure successfully completed.

and no trace files are generated in ../udump

I am quite sure that I supply correct sid and serila#
Any ideas ?

Thanks

Check the type of connection of sid 42 in v$session. If it's SHARED then no trace file will generate by dbms_system.set_sql_trace_in_session.

Hi

work out the following.....
"GRANT EXECUTE ON DBMS_SYSTEM TO username;"
user you want to trace..
then execute the procedure.
gopu

Quote:

---

Originally Posted by gopu_g

Hi

Grant execute on the user you want to trace.
GRANT EXECUTE ON DBMS_SYSTEM TO username;
then execute the procedure.
gopu

---

I wouldnt grant execution privilege on a system package such as this one to a user

No it does not work.

The strange thing is that it worked before on a different systems.

BTW this is oracle 10.2.0.2.0

Any more ideas ...
I run it from the script

This is the way I get sid&serial num


select a.username,a.sid ,a.serial#
into :v_username,:v_sid,:v_serial_num
from v/$session a, v/$sqlarea b, v/$process c
where a.sql_address=b.address
and c.ADDR=a.PADDR
and c.SPID='$PID';

Quote:

---

Originally Posted by zam

No it does not work.

The strange thing is that it worked before on a different systems.

BTW this is oracle 10.2.0.2.0

Any more ideas ...
I run it from the script

This is the way I get sid&serial num


select a.username,a.sid ,a.serial#
into :v_username,:v_sid,:v_serial_num
from v/$session a, v/$sqlarea b, v/$process c
where a.sql_address=b.address
and c.ADDR=a.PADDR
and c.SPID='$PID';

---

Have you check connection type of session whether it is SHARED or DEDICATED in v$session.

Yes I did check - it's dedicated. I am testing my script and running my owen session for that.

I am definetly missing something
I have just run the same on the system it worked before and got the same result

1. login as sysem in db and run sql - select count(*) from dba_objects a, dba_objects b; just to run for a while
2. login as sysdba from different session
3. Get SID and SERIAL#
4. EXECUTE SYS.dbms_system.set_sql_trace_in_session (14, 470, TRUE);
PL/SQL procedure successfully completed.
5. Checked udump dir .. -nothing there :confused:

Check the user_dump_dest. (show parameter user_dump_dest)

If not defined in parameter file then you have to check at $ORACLE_HOME/rdbms/trace folder

Sorry ...:o
Pando was right I have to GRANT EXECUTE ON DBMS_SYSTEM to user.
I did it first and it didn't work for me probably just was looking not in a right place. Now it works :cool:
Thanks !

Quote:

---

Originally Posted by pando

I wouldnt grant execution privilege on a system package such as this one to a user

---

Hi what i meant is not to grant execution privilege to user,is to grant execute to dbms_system to user,but by mistake i wrote like that,i corrected that.

gopu

Thank you gupu. Appreciate your assitance.