Dev's Weblog

Reading time: 1 - 2 minutes

Sometimes it is necessary to automatically trigger SQL trace. Automatically here means that code must be added somewhere.

The simplest approach is to create a logon trigger at the database level. To avoid enabling SQL trace for all users, I usually suggest creating a role (named sqltrace in the following example) and temporarily granting it only to the user utilized for the test.

CREATE ROLE sqltrace;

CREATE OR REPLACE TRIGGER enable_sqltrace AFTER LOGON ON DATABASE
BEGIN
IF (dbms_session.is_role_enabled('SQLTRACE'))
THEN
EXECUTE IMMEDIATE 'ALTER SESSION SET timed_statistics = TRUE';
EXECUTE IMMEDIATE 'ALTER SESSION SET max_dump_file_size = unlimited';
dbms_monitor.session_trace_enable;
END IF;
END;
/

So whenever user with sqltrace role logs in sql tracing will be started by above trigger.