30-Jul-2008: Oracle SPY

Oracle SPY

-- Dennis Yurichev <dennis(@)conus.info> http://blog.yurichev.com

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,, and, 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 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:


Update: this utility was superseded by generic tracer.

This open sourced site and this page in particular is hosted on GitHub. Patches, suggestions and comments are welcome.

→ [list of blog posts]

Please drop me email about any bug(s) and suggestion(s): dennis(@)yurichev.com.