1.First enable SQL trace by using following command.All commands to be executed by SYS user.
> ALTER SESSION SET sql_trace=TRUE;
2.Execute the procedure or event to which trace is to be generated.
3.> ALTER SESSION SET SQL_TRACE = FALSE;
to check the path where the file is generated
Execute below commands
> show parameter USER_DUMP_DEST;
4.trc files are generated at path in step 2.
5.Convert .trc files into .txt using below commands.
> tkprof d:filename.txt output= d:filename.txt
Assumed file is generted at d: drive
For Toad users can generate trace reports using – Database->Session Browser-> (List of sessions on database -> start/stop trace are listed using green and red spheres)