Sample Code to create Logon triggers to trace particular sessions

create or replace trigger set_trace after logon on database

   begin

    if user  in ('SA') then

     execute immediate 'alter session set timed_statistics=true';

     execute immediate 'alter session set max_dump_file_size=unlimited';

     execute immediate 'alter session set sql_trace=true';

    end if;

   exception

    when others then 

    null;

  end;

  /

SQL> exec dbms_monitor.session_trace_enable(248);

PL/SQL procedure successfully completed.

SQL> -- execute some sql in the other session

SQL> -- turn off trace

SQL> exec dbms_monitor.session_trace_disable(248);

Capturing details in custom tables through triggers

CREATE OR REPLACE TRIGGER orders_after_delete
AFTER DELETE
    ON orders
    FOR EACH ROW

DECLARE
    v_username varchar2(10);

BEGIN

    -- Find username of person performing the DELETE on the table
    SELECT user INTO v_username  FROM dual;

    -- Insert record into audit table
    INSERT INTO orders_audit
     ( order_id,
       quantity,
       cost_per_item,
       total_cost,
       delete_date,
       deleted_by)
    VALUES
     ( :old.order_id,
       :old.quantity,
       :old.cost_per_item,
       :old.total_cost,
       sysdate,
       v_username );

END;

OK, one final way to set and get trace, is to use the SQL*Plus AUTOTRACE facilities. There are a few settings that you can use.

These are as follows:

set autotrace off - The default - no output

set autotrace on explain - This shows only the optimizer path

set autotrace on statistics - This only shows SQL statistics

set autotrace on - Includes both of the above

set autotrace traceonly - As above but the query output is not displayed