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?
Add a comment
|
3 Answers
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
4 Comments
David Aldridge
When you execute "DBMS_SESSION.SESSION_TRACE_ENABLE(waits => TRUE, binds => FALSE); "?
David Aldridge
How are you executing it, and from what environment? In PL/SQL you would omit the EXECUTE, which is generally used in client tools as a shortcut for wrapping a PL/SQL command in BEGIN ... END;
Maxime Pacary
@Nap
SELECT VALUE FROM V$PARAMETER WHERE NAME = 'user_dump_dest'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.
Comments
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.
