An engineer at one of my customer projects was having some difficulties with a third party program and Oracle. The application was referencing objects that didn’t exist, and throwing ORA- errors. There were difficulties in getting the attention of the Oracle DBA group to help troubleshoot the issue, so he found a clever way to find his information. He plopped in p6spy.

P6Spy is an open source framework to support applications that intercept and optionally modify database statements. The P6Spy distribution includes the following modules:

P6Log. P6Log intercepts and logs the database statements of any application that uses JDBC. This application is particularly useful for developers to monitor the SQL statements produced by EJB servers, enabling the developer to write code that achieves maximum efficiency on the server. P6Spy is designed to be installed in minutes and requires no code changes.

P6Outage. P6Outage detects long-running statements that may be indicative of a database outage proble and will log any statement that surpasses the configurable time boundary during its execution. P6Outage was designed to minimize any logging performance penalty by logging only long running statements.

He indicated it took only a few minutes to plop it in the application, and troubleshoot which object was missing. Helpful little utility if you’re in a bind. Thanks for the tip Russ!