OCISPY - ORACLE OCI client side tracing and monitoring utility.

Main features :

• OCISPY can trace and monitor client side OCI calls.
• This makes possibly to see SQL statements, bind/define parameters and a lot of other useful information.
• OCISPY uses "stderr" as an output stream by default.
• Output can be redirected to a file by setting OCISPY_LOG parameter in the configuration file.
• You can choose groups of functions you want to trace by setting FUNCTION_TYPE parameter. Allowed values are: ADVANCED_QUEUING, HANDLE, DESCRIPTOR, BIND, DEFINE, DESCRIBE, CONNECT, AUTHORIZE, INITIALIZE, LOB, STATEMENT, TRANSACTION, MISCELLANEOUS.
• Only the client for ORACLE v8i and higher is supported.
• Profiling is planned.

• What's New
• Documentation
• Examples
• Output example
• Known bugs and limitations
• Download OCISPY
• Installation
• E-mail for contacts
• Disclaimer
• To do

What's New

• Jan 11, 2002: version 0.2.8. It's become smaller. A lot of useless wrappers are not included now.
• Jan 06, 2002: version 0.2.7. Linux alpha version. Still alpha ...
• Dec 22, 2001: version 0.2.6. Bug fixes and yet more info ...
• Dec 18, 2001: version 0.2.5. Bug fixes. You can see a text of error messages that were issued by ORACLE now.
• Dec 17, 2001: version 0.2.4. Bug fixes.
• Dec 14, 2001: version 0.2.3. It is mostly a maintenance release, but an amount of output information was increased.
• Dec 06, 2001: version 0.2.2. It shows attribute values in case of setting now.
• Dec 02, 2001: version 0.2.1 is released. Configuration parameter FUNCTION_TYPE is added. This parameter describes groups of functions you want to trace. DLL was renamed from ocispy.dll to ocispy8i.dll (because a future ocispy9i.dll will not work with OCI v8i).
• Nov 09, 2001: version 0.2 is released. Configuration file support is added. First parameter to set is "ocispy_log" - log file name.
• Oct 30, 2001: First initial version 0.1 is released.

Different ways to do this

Set sql_trace = true in:
• alter session ... or dbms_session.set_sql_trace - for a current session;
• sys.dbms_system.set_sql_trace_in_session - for another session;
• init.ora - for all sessions;

Look into %user_dump_dest%/*.trc on a server.

Documentation

• If you want to understand what mean all these functions and parameters, try to read "Oracle Call Interface Programmer’s Guide"

Examples

• Correct settings for Windows:

set ORA_OCI_UCBPKG=ocispy8i
sqlplus
or:
set ORA_OCI_UCBPKG=ocispy8i
sqlplusw

• Note: value of the ORA_OCI_UCBPKG variable is case sensitive !!!
• Wrong ideas:

set ORA_OCI_UCBPKG=C:\Oracle\Ora81\bin\ocispy8i.dll
or:
set ORA_OCI_UCBPKG=ocispy8i.dll

• Correct settings for Unix (bash):

export ORA_OCI_UCBPKG=ocispy8i
sqlplus

Output example

• FUNCTION_TYPE has default value.
• FUNCTION_TYPE = BIND DEFINE AUTHORIZE STATEMENT TRANSACTION HANDLE DESCRIBE.

Known bugs and limitations

• Trace file can be found in a program working directory, in the root of disk C:\ or in ORACLE_HOME\bin.

Download OCISPY

• ocispy8i-0.2.6.zip Windows version. ORACLE 8i or 9i.
• ocispy8i-0.2.8-i386-linux.tar.gz Linux version. ORACLE 8i. (tested only with 8.1.7)

Installation

• Windows.
• Unzip archive to a temporary directory.
• Copy file ocispy8i.dll into ORACLE_HOME/bin.
• Set environment variable ORA_OCI_UCBPKG to "ocispy8i". (But not to "C:\Oracle\Ora81\bin\ocispy8i.dll" or to "ocispy8i.dll"). Note: value of the ORA_OCI_UCBPKG variable is case sensitive !!!
• If you want to trace GUI applications, you can set up logging file name by setting parameter OCISPY_LOG in the configuration file to the log file name.
• The configuration file must have name "ocispy.cfg".
• OCISPY will look for it in your working directory or in a root directory of a drive "C:".
• Enjoy !!!
• Unix.
• Unzip archive.
• Make a copy of the file libocispy.so.x.x.x somewhere you want to keep it.
• Make a symbolic link $ORACLE_HOME/lib/ocispy8i.so.1.0 (with exactly same name and same case) to your file.
• Set environment variable ORA_OCI_UCBPKG to "ocispy8i".
• OCISPY will look for configuration file in your working directory or in your home directory.

FAQ

• Q1: Is the source code of OCISPY available?

  A1: Currently I have no plans to release the ocispy source code for any purposes including porting to other computer platforms.

• Q2: Is this program thread safe?

  A2: No.

E-mail for contacts

• If you have any questions, bug reports or comments regarding OCISPY, please send e-mail to sergey UNDERSCORE sikorsky AT yahoo DOT com. (Do not forget to fix an e-mail :-) )

Disclaimer

• Copyright 2001 by Sergey Sikorskiy

• THIS SOFTWARE IS PROVIDED ``AS IS'' AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE AUTHOR OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
• THE SOFTWARE IS NOT DESIGNED OR INTENDED FOR USE IN ON-LINE CONTROL OF AIRCRAFT, AIR TRAFFIC, AIRCRAFT NAVIGATION OR AIRCRAFT COMMUNICATIONS; OR IN THE DESIGN, CONSTRUCTION, OPERATION OR MAINTENANCE OF ANY NUCLEAR FACILITY. THE AUTHOR DISCLAIMS ANY EXPRESS OR IMPLIED WARRANTY OF FITNESS FOR SUCH USES. YOU MUST REPRESENT AND WARRANT THAT YOU WILL NOT USE THE SOFTWARE FOR SUCH PURPOSES.