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.

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.