How do you monitor specific SQL statement of a specific user ?

Question

We have a few standalone users here which cause high server loads for several 11.1.0.7 Oracle's. I spoke with them a few times but it seems that their queries are causing high loads and probably bottlenecks

Is there any option in Oracle to monitor or see which SQLs they running ? I'm not talking about only the ones which cause high loads, although they're the most important ones I would like to see all of their queries.

• Philᵀᴹ: Are you running Enterprise Edition & do you have the diagnostic pack licensed?

Answer 1

You might want to look into AUDIT ALL STATEMENTS BY user. As per Oracle's documentation, audit all statements by ... audits all top-level SQL statements executed. Top-level SQL statements are issued directly by a user. SQL statements run from within a PL/SQL procedure or function are not considered top-level statements. Therefore, this clause does not audit the statements executed within PL/SQL procedures or functions. However, the execution of the PL/SQL procedure or function itself is audited. This clause is useful if you want to audit all the statements in a specific environment, regardless of other auditing configurations that are system wide or user specific.

• dotdot: Hi. Thanks for the quick answer but this does not answer my question :( I do not wish (or can...) limit those users, as they are special enough to have their own account to perform their own tests. The reason I wish to monitor their SQLs is because I can't speak with them every time they execute a killer SQL which blocks or makes the db high load.
• René Nyffenegger: I see. I have changed the answer accordingly.