DBMS_SESSION.SET_SQL_TRACE Procedure

This procedure enables/disables events in another session

Argument Name	Type	In/Out	Default?
SQL_TRACE	BOOLEAN	IN

To enable event 10046 level 1 trace in the current session use:

EXECUTE dbms_session.set_sql_trace (TRUE);

To disable trace in the current session use:

EXECUTE dbms_session.set_sql_trace (FALSE);

Note that the DBMS_SESSION package is created using invoker rights. Therefore to call SET_SQL_TRACE from a stored procedure or function define using AUTHID CURRENT_USER e.g,

CREATE OR REPLACE PROCEDURE procedure1 
AUTHID CURRENT_USER IS
BEGIN
  DBMS_SESSION.SET_SQL_TRACE (TRUE);
END;
/