Oracle XE query log

In Postgres you can switch on query logging, resulting in a file containing all queries issued by any client.

Is there开发者_如何学运维 a similar possibility in Oracle XE? How do I switch it on and where do I find the resulting file?

---

You would:

alter session set sql_trace=true;

The trace file will be in the udump subdirectory under the installation directory.

Edit: Actually the docs say that sql_trace is deprecated in 10g: http://download.oracle.com/docs/cd/B19306_01/server.102/b14237/initparams205.htm#REFRN10208

It looks like DBMS_SESSION is the way to go now:

eg.

EXECUTE DBMS_SESSION.SESSION_TRACE_ENABLE(waits => TRUE, binds => FALSE);

http://download.oracle.com/docs/cd/B19306_01/server.102/b14211/sqltrace.htm#CHDDGCCB

---

If you're targeting a specific application, you might find useful to use p6spy which is "an open source framework that intercept and optionally modify database statements"

Can be used with JBoss, ATG, Orion, JOnAS, iPlanet, WebLogic, WebSphere, Resin and Tomcat.

---

If you log into the web admin interface, and go into administration, you'll "Top-SQL" .... It has a search functionality, you can supply part of the query your looking for. I find that to be very helpful.