Oracle SQL Query logging

Question

I'm using Oracle 11g Standard Edition.

I would like to log all SQL queries, that are being executed by users, into a table.

How can this be done?

Answer 1

For QUICK, EASY logging of SQL, try my monitoring answer here. Not for long-term logging, but works great just to see what is going on in a small time window. :-)

Answer 2

If you're using a modern version of the database (9i or later) and you have an Enterprise Edition license you can use Fine-Graining Auditing. It allows us to audit user queries at a very low level of granularity, through defined policies.

To capture SQL text and bind variables you will need to set the AUDIT_TRAIL parameter appropriately when adding an FGA Policy. Find out more.

---

"i'm using an 11g standard, so auditing functions are not supported."

Not exactly. The AUDIT command is part of the standard Oracle build, but it only allows us to capture when a given user issues a SELECT against a given table. But, yes, to find out exactly what they are selecting requires Enterprise Edition license.

Also there is no ON SELECT trigger, so we cannot roll our own.

---

"So can i use AUDIT command in the standard edition? ... But then a consultant told me, that i cannot use it without paying enterprise license? "

Speaking as a consultant myself, I do have to say those guys don't always know what they are talking about.

So let's be clear:

• the AUDIT command is part of Oracle SQL. It is usable with the Standard Edition. In fact since 11g it is enabled by default. It audits general activity. Find out more.
• Fine Grained Auditing is a PL/SQL package with is only usable if you have the Enterprise Edition. It allows us to audit user activity at a very low level. Find out more.