Alfred Mickautsch, January 29, 2002 - 12:15 pm UTC
Thanks a lot.
how to find out the state of SQL_TRACE for a session
John Chung, January 29, 2002 - 1:12 pm UTC
First off, thank you for the useful script.
One followup question:
The function session_trace can only be compiled by the SYS user. If one compiles it with a user account that has the DBA role only, one will get
'PL/SQL: ORA--00942: table or view does not exist
'PLS-00364: loop index variable X is invalid' error, indicating the implicit cursor is invalid. How can one get around it without invoking the script from SYS?
John
January 30, 2002 - 7:02 am UTC
or just have sys grant select on v_$session, v_$process, v_$parameter to the owner of the procedure.
Or have sys create a view that selects from v$..... ( use the query i have in the function there, call it say "SESSION_TRACE_VIEW" ) and have sys grant select on that single view to the owner of the procedure.
It was very good, but maybe ....
Chiappa, January 30, 2002 - 10:05 am UTC
The proposed way work , for sure, but a friend of mine (an old orassaurus from v6) suggests :
ORADEBUG SETORAPID, make "current session" whatever you like, and then check for event 10046 with DBMS_SYSTEM.read_ev .
I can´t test this right now , but if anyone is interested, try and let us know !
RE: It was very good, but maybe...
Mark A. Williams, January 31, 2002 - 3:57 pm UTC
How does this help determine who started the tracing??? That is what the original poster was after, right? Am I missing something in the suggestion?
- Mark
Mea Culpa
Mark A. Williams, January 31, 2002 - 4:00 pm UTC
Cancel my last question - haven't had my coffee yet. A re-read of the original poster's question was in order...
- Mark
DBMS_SYSTEM SQL TRACING OFF
Venkateswara Reddy, March 11, 2002 - 12:28 pm UTC
Hi tom
Today I have enabled trcing for a particular sessin using DBMS_SYSTE.SET_SQL_IN_TRACE. But after tracing we have swithced off all the system (end of the day). Here what I executed for the trcing user using SID,SERIAL# I din't found after restarging the system. for the same users (os users) differenct sid's allocated. But in UDUMB directory still trace files are creating. I would like to know How can I trace off particulare session which already I have enabled trace after some time.
Thanks in advance
Reddy
Tracing another session with binds and waites
Hatem, June 21, 2004 - 8:50 am UTC
Hi
How to trace another sessions with binds and waits
like ALTER session SET EVENTS '10046 trace name context forever, level 12'; but for another session?
Regards
Hatem
June 21, 2004 - 9:32 am UTC
sys.dbms_system.set_ev (set event) can do that.
PROCEDURE SET_EV
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
SI BINARY_INTEGER IN
SE BINARY_INTEGER IN
EV BINARY_INTEGER IN
LE BINARY_INTEGER IN
NM VARCHAR2 IN
PROCEDURE SET_INT_PARAM_IN_SESSION
si = sid
se = serial#
ev = event = 10046
le = level = 12
nm = name = ''
Maybe a related question...?
Matt, June 21, 2004 - 7:41 pm UTC
I have also been wondering about other session settings. Is it possible to find out whether a variable has been modified in your own session and it's value? How about if it was changed in another session?
Thanks.
June 21, 2004 - 8:56 pm UTC
"a variable"?
Clarification
Matt, June 21, 2004 - 10:50 pm UTC
Apologies for not being clear.
A session may modify a database parameter (through the execution of an "alter session set <param> = <value>") provided the session owner has the "alter session' privilege.
Is it possible to find out the names and values of any database parameters that may have been modified (via "alter session") within any sessions in the database?
Thanks.
June 22, 2004 - 7:41 am UTC
in 10g, this has been exposed.
in 9i and before, you cannot see other sessions settings -- only *some* of your own changes and they would be reflected in v$parameter. Consider:
ops$tkyte@ORA9IR2> show parameter sort_area_size;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
sort_area_size integer 524288
ops$tkyte@ORA9IR2> show parameter sql_trace;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
sql_trace boolean FALSE
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> variable n number
ops$tkyte@ORA9IR2> exec sys.dbms_system.read_ev( 10046, :n );
PL/SQL procedure successfully completed.
ops$tkyte@ORA9IR2> print n
N
----------
0
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> alter session set sort_area_size = 1024000;
Session altered.
ops$tkyte@ORA9IR2> alter session set sql_trace=true;
Session altered.
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> variable n number
ops$tkyte@ORA9IR2> exec sys.dbms_system.read_ev( 10046, :n );
PL/SQL procedure successfully completed.
ops$tkyte@ORA9IR2> print n
N
----------
1
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> show parameter sort_area_size;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
sort_area_size integer 1024000
ops$tkyte@ORA9IR2> show parameter sql_trace;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
sql_trace boolean FALSE
<b>we can see sort_area_size in v$parameter for our session, but not sql_trace -- sql_trace is really an event in disguise and we can see it is on by using dbms_system to read the setting of that event</b>
Rmz, June 22, 2004 - 4:24 am UTC
Hi Tom
How to turn sys.dbms_system.set_ev (set event) off after traceing the session?
Please advice
June 22, 2004 - 8:36 am UTC
exec sys.dbms_system.set_ev( &sid, &serial, 10046, 0, '' );
set it back to zero.
Tracing status
Sinan Topuz, November 27, 2007 - 10:45 am UTC
Tom, this is really helpful. I have a question though. My system is on 8.1.7.4 and Windows Server 2000. Let's say I initiated tracing with the following command from SQL *Plus by SYS user
exec sys.dbms_system.set_ev(34, 20716, 10046, 12, '');
The function that you suggest finds out very well that the file exists so it means that we are (or did already) tracing that session. If I stop tracing with
exec sys.dbms_system.set_ev(34, 20716, 10046, 0, '');
the file is still there, so the function still assumes that the tracing is on.
Is this the best and only way of checking the status of tracing on 8.1.7 on NT?
Thanks,
Sinan
November 27, 2007 - 3:55 pm UTC
yes
?
pedro, November 09, 2009 - 3:22 pm UTC
hi tom
where are the trc generated
exec sys.dbms_system.set_ev(34, 20716, 10046, 12, '');
November 11, 2009 - 2:41 pm UTC
in the user dump destination if you are using a dedicated server and in the background dump destination if you are using a shared server/job queue process (a background process)
Jimmy Brock, November 11, 2009 - 3:53 pm UTC
Tom,
Where I work I do not have access to the OS, so I cannot access trace files directly. I have to open up a ticket to have a trace file send to me. Does Oracle have a tool that provides direct access to trace files?
November 15, 2009 - 1:18 pm UTC