Oracle Database 11g Enterprise Edition Release 220.127.116.11.0 - 64bit Production
PL/SQL Release 18.104.22.168.0 - Production
CORE 22.214.171.124.0 Production
TNS for 64-bit Windows: Version 126.96.36.199.0 - Production
NLSRTL Version 188.8.131.52.0 - Production
Im trying to find a way to set up an SQL trace (similar to SQL Profiler under SQL Server) through the Oracle Enterprise Manager.
Ive managed to do this manually, outputting to a file with the following:
To get the process I want I execute:
select sid, serial#, username, status, process from sys.v_$session where username = 'MYUSER'
Alternatively I can also do it 'globally' with the following but this produces lots of trace files...
> alter session set sql_trace = true;
These successfully produce trace file(s) in my C:\oracle\diag\rdbms\...\trace directory.
I'm sure there is a way though of setting this up and viewing it graphically in real time (just as you can with SQL Profiler under SQL Server) through the Oracle Enterprise manager but I am having no luck.
If I select the Performance tab, scroll down to Additional Monitoring Links and choose SQL Monitoring, this looks like what I am looking for but how do I get it to show the SQL actioned against the database/schema? -
i.e. I run a SELECT statement in SQL Developer and want to see this in the SQL Monitor?, when I do this though it doesn't pick it up.
I'm tearing my hair out here, looked(am looking) at all the docs I can find but I can't get it working.
Wow - 80 views and still no expert has commented on this.
This could mean 1 of 2 things:
1 - What I am asking is so simple and easy to do that its not even worth replying - i.e RTM (I would - please point me to the part of the manual that deals with GUI real time tracing - I can't find it!)
2 - This isn't easy at all and no one has successfully done what I want either.
Surely what I ask is possible in Oracle isn't it?
Anyone who has used SQL Profiler in SQL Server knows what a valuable tool it is.
Come on you Oracle people, help this SQL Server convert out...
Have you tried "Search Sessions" under the Performance tab, then clicking on the 'Activity' tab for the selected session? If you then want a trace file using OEM then in the top right hand corner click on "Enable SQL Trace", but remember to disable the trace when you've finished (otherwise your trace file will keep growing!).
Or if you want an overview of everything that's going on, with the option to 'dive in' on specific transactions/users, try clicking on the 'Top Activity' link (under 'Additional Monitoring').
If you want something more detailed than SQL Trace then have a look at SQL Trace Analyzer (needs to be downloaded & installed as it's not part of the Oracle build).
90% of users' problems can be resolved by punching them - the other 10% by switching off their PCs.