Starting and Stopping Traces on Sessions in Oracle
To be able to start and stop traces on a session in an Oracle database, the user must first be logged in with DBA privileges to gain access to the tables necessary to find the running sessions. To connect to Oracle with DBA privileges using RazorSQL, make sure to select the "SYSDBA" connect as option when creating the Oracle connection profile on the Connections -> Add Connection Profile menu option.
After connecting with DBA privileges, the following query can be used to find the running sessions:
SELECT
*
FROM
SYS.v_$session s,
SYS.v_$sess_io io,
SYS.v_$process p
WHERE
s.sid=io.sid AND
s.paddr = p.addr
The key columns to look at in the above query are the SID, SERIAL#, and USERNAME columns. After finding the session from the query results after executing the above query, the SID and SERIAL# are needed to be able to start a trace on the session. The following command can be used to start the trace. The first value is the SID, and the second value is the SERIAL#.
BEGIN
SYS.DBMS_SYSTEM.set_sql_trace_in_session(52, 3333, TRUE);
END;
The trace can be stopped by executing the following command:
BEGIN
SYS.DBMS_SYSTEM.set_sql_trace_in_session(52, 3333, FALSE);
END;
