Internal SQL statements tracer

[Dennis Yurichev]

Hi.

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

Oracle SPY
-- Dennis Yurichev <den...@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)

[DA Morgan]

Dennis Yurichev wrote:
> Hi.
>
> Here is an utility I wrote ....

Spam is spam is spam. Please post promotional items in the
appropriate usenet group: c.d.o.marketplace.

Thank you.
--
Daniel A. Morgan
Puget Sound Oracle Users Group
www.psoug.org

[ErikYkema]

Is it spam? Not to me.