This content has been marked as final. Show 2 replies
I'm not quite sure what this has to do with database security. I suspect it would be better off in the JDBC forum or the Database General forum.
You can trace the session from the database server. If you're looking at P6Spy, though, that implies that you are only interested in logging the activity of a Java application that uses JDBC to connect to the database. If so, you should be able to use the debug JDBC drivers and enable logging there. There is a chapter in the JDBC Developer's Guide on Diagnosability in JDBC that walks through how to configure the debug JDBC driver to log.
DIVI wrote:The obvious question is why? What need or requirement are you trying to satisfy?
P6Spy will do my job but for some reasons i could't able to add that, so i am searching for a tool which can do that job at oracle side. .
There are a number of s/w layers between client and server, and on the server. Many of these can be configured for some kind of tracing. Justin mentioned JDBC. ODBC has a similar option - enabling tracing in the driver. ODBC also has a debug driver interface - where the client connects to the debugger and the debugger proxies calls to the actual driver.
The same approach can be used on the server - running the Listener on localhost only and putting a SQL*Net proxy on port 1521 that proxies calls to the Listener on localhost. The proxy can be self-rolled using Pascal, Perl, C, etc (not that complex to write a tcp proxy). And the proxy can record client calls containing SQL statements (OCI prepare statement calls).
A logon trigger can enable SQL tracing for a session - resulting in all SQLs of that session to be dumped to trace file (can be an expensive ito diskspace). Auditing in the database can be used. Logminer can be used for post-mortem analysis.
If a PL/SQL interface is used as a SQL abstraction layer by the client, instrumentation in the PL/SQL layer can be used.
If the intention is for dealing with SQL performance related issues and a diagnostic pack license has been purchased, AWR can be used.
There are even methods using VPDB features that can be considered to trace SQLs.
However - SQL tracing is not something that should be considered without serious thought and valid justification. Oracle databases can run (and often do run) 100's of SQLs per second. Throwing tracing into the mix can be a very expensive and totally unwarranted overhead.