Use a logon trigger for the specific user to enable SQL tracing : it generates a trace file in the USER_DUMP_DEST location.The trace file contains all the SQL statements executed by the user after logon.
Create the following trigger in SYS schema:
create or replace trigger SCOTTLOG
after logon
on scott.schema
begin
-- optionally add a tag to the trace file name to make the files discernable:
execute immediate ' ALTER SESSION SET TRACEFILE_IDENTIFIER =''SCOTT''';
-- enable sql_trace for this session:
execute immediate 'ALTER SESSION SET SQL_TRACE TRUE';
end;
/
Note that all the SQLs of SCOTT user after logon will be traced and if the required collection is sufficient, disable the trigger to stop the collection and generation of trace files.
SQL> alter trigger scottlog disable;
Note
i) Overhead involved is trace file generation in user_dump_dest.
ii) Grant the CREATE SESSION system privilege to the user account rather than granting the CONNECT role. This is necessary because in the versions older than 10gR2 the CONNECT role includes the ALTER SESSION system privilege and the user can disable sql tracing after logon. To avoid this, do not grant the CONNECT and RESOURCE roles , but grant the necessary privileges explicitly.
Ref: 309798.1
