Sql statement tracing using tkprof utility

How to get queries, which is executed by particular user:-

1.Change the timed_statistics parameter to TRUE:-

SQL> alter system set TIMED_STATISTICS=TRUE;

2.Turn tracing on user for session level:-

SQL> alter session set SQL_TRACE=TRUE;

Or

3.As a DBA, execute the below command to enable sql trace for particular user:-

Get the sid and serial# from the V$session view.

eg:-

select sid,serial#,username from v$session
where machine='A4MD08060';


select sid,serial#,username from v$session
where username='NAVPROD';

select sid,serial#,username from v$session
where username not in ('SYS');

Enabling trace:
SQL> exec DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION(sid,serial#,true);

eg:-

exec DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION(93, 24638, true);
exec DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION(81,64882, true);
exec DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION(96,20312,true);

Disabling trace:
SQL> exec DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION(sid,serial#,false);

eg:-

exec DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION(93, 24638, false);
exec DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION(81,64882, false);
exec DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION(96,20312,false);



3.Then, see the user dump destination for trace file.

sqlplus sys as sysdba

sho parameter dump;

4.Execute the below command to analyze and to create insert script for generating sql statements from trace file:-

$ tkprof insert=tkprof_table.sql sys=no

eg:-
tkprof orap_ora_12523.trc orap_ora_12523.txt insert= orap_ora_12523.sql sys=no
tkprof orap_ora_19752.trc orap_ora_19752.txt insert= orap_ora_19752.sql sys=no


It will create in text file and script for to create tkprof_table.

5.Execute the tkprof_table.sql file in any user schema.

6.It will create tkprof_table, with contents.

7.find out the user_id for that particular user from dba_users

eg:-select user_id from dba_users
where username='NAVPROD';

8.Execute the below query to get the queries of particular user.

eg:-

SQL>set long 9999
set heading off
set pages 0
spool sql_statements.txt
select sql_statement from tkprof_table where user_id =;

Note:-

if you want it in .cvs or .xls use dbvisualiser or other tools and if you dont have those tools you can use command line to do the same.

For more info on command line check the posting "Oracle tables to Excel".