2 Replies Latest reply: Dec 11, 2012 12:02 AM by Billy~Verreynne RSS

    Is there any tool available to record all queries which are hitting Oracle

    DIVI
      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. .

      Please suggest me if any such kind of tools are there.
        • 1. Re: Is there any tool available to record all queries which are hitting Oracle
          JustinCave
          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.

          Justin
          • 2. Re: Is there any tool available to record all queries which are hitting Oracle
            Billy~Verreynne
            DIVI wrote:
            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. .
            The obvious question is why? What need or requirement are you trying to satisfy?

            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.