Internal SQL statements tracer

Hi.

Here is an utility I wrote and use, maybe someone else will find it useful too.

Oracle SPY

• Dennis Yurichev <dennis@conus.info> http://blogs.conus.info

This is win32 utility which intercepts internal Oracle RDBMS function calls to rpisplu(), kprbprs(), opiprs() and OCIStmtPrepare() - all these 4 functions used in internal SQL processing. All they are actually "parse" command from different RDBMS layers.

Thereby, this utility allow us to see all (As I know) internal SQL executions.
It may be used for debugging, educational or any other purposes.

It was tested on Oracle 9.2.0.8, 10.1.0.5, 10.2.0.3 and 11.1.0.6.0, of course, for win32 platform.
Operation systems tested on: Windows Vista, Windows 2008 Server, Windows 2003 Server, Windows XP SP2 and SP3, Windows 2000 Server.

Before you run it, ORACLE_HOME environment variable should be set. Also, ORACLE_HOME\bin path should be present in %PATH% environment variable.

After start, utility attaches to oracle.exe process and allow us to see these internal calls. Press Ctrl-C (once) to detach from Oracle process.
Please note: detaching is not working in Windows 2000, so all utility can do is to kill Oracle process.

If Oracle RDBMS version 11.1.0.6.0 is used, Oracle internal process name will be visible also at each SQL statement. Otherwise, only win32 thread ID will be visible. Windows thread ID can be converted to Oracle process name using this query:

"select spid, program from gv$process;"

Utility is not intended to use on production servers. But if someone consciously willing to use it, one should backup database. Utility cannot be stable yet, at this level of development.

Source code was initially compiled by MSVC 2008.

Examples, which were recorded on freshly installed 11g win32:

STARTUP_ospy.log: instance service startup SCOTT_LOGON_ospy.log: user SCOTT logon.
VERSION_ospy.log: during "select * from v$version" query. SHUTDOWN_ospy.log: instance shutdown.

Homepage:
http://blogs.conus.info/node/9
Download:
http://blogs.conus.info/sites/default/files/ospy.zip (Source code included) Received on Thu Jul 31 2008 - 01:53:56 CDT