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;
As a DBA, execute the below command to enable sql trace for particular user:-
SQL> exec DBMS_SESSION.SET_SQL_TRACE_IN_SESSION(sid,serial#,true);
Get the sid and serial# from the V$session view.
3. Then, see the user dump destination for trace file.
4. Execute the below command to analyze & create insert script for the trace file:-
$tkprof (sql_trace_file_name) (any_text_file_name) insert=tkprof_table.sql
It will create in text file and script for to create tkprof_table.
5. Execute the tkprof_table.sql file in sys user schema.
6. It will create tkprof_table, with contents.
7. Execute the below query to get the queries of particular user.
(before get the user_id from the dba_users view)
set long 5000
select sql_statement from tkprof_table where user_id =
