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?
+1
A:
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%5F01/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%5F01/server.102/b14211/sqltrace.htm#CHDDGCCB
David Aldridge
2009-10-15 08:24:29
Seems to me that this is what I search. But when I execute the query, I get an 0RA-00900 error (invalid query).
chris
2009-10-19 08:03:19
When you execute "DBMS_SESSION.SESSION_TRACE_ENABLE(waits => TRUE, binds => FALSE); "?
David Aldridge
2009-10-19 09:18:29
Unfortunately, this results in an ORA-00900 error as well.
chris
2009-10-21 08:51:08
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;
David Aldridge
2009-10-21 09:48:47
A:
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.
Jay
2009-10-16 14:33:49