sqlplus /nolog
conn sys/1234@XE as sysdba
drop user test cascade;
create user test identified by test;
grant connect, resource to test;
grant alter session to test;
grant all on plan_table to test;
grant select on v_$session to test;
grant select on v_$process to test;
grant select on v_$parameter to test;
conn test/test@XE
alter session set timed_statistics = true;
alter session set max_dump_file_size = unlimited;
alter session set tracefile_identifier = 'test_1';
CREATE TABLE sql_trace_test (
  id  NUMBER,
  description  VARCHAR2(50)
);
CREATE OR REPLACE PROCEDURE populate_sql_trace_test (p_loops  IN  NUMBER) AS
  l_number  NUMBER;
BEGIN
  FOR i IN 1 .. p_loops LOOP
    INSERT INTO sql_trace_test (id, description)
    VALUES (i, 'Description for ' || i);
  END LOOP;
  
  SELECT COUNT(*)
  INTO   l_number
  FROM   sql_trace_test;
  
  COMMIT;
  
  DBMS_OUTPUT.put_line(l_number || ' rows inserted.');
END;
/
alter session set events '10046 trace name context forever, level 8';
EXEC populate_sql_trace_test(p_loops => 5);
select * from dual;
alter session set events '10046 trace name context off';

SELECT s.sid,
       s.serial#,
       pa.value || '/' || LOWER(SYS_CONTEXT('userenv','instance_name')) ||    
       '_ora_' || p.spid || '.trc' AS trace_file
FROM   v$session s,
       v$process p,
       v$parameter pa
WHERE  pa.name = 'user_dump_dest'
AND    s.paddr = p.addr
AND    s.audsid = SYS_CONTEXT('USERENV', 'SESSIONID');