Using ODBC Trace or Oracle Trace to find cause of error?

If a block in the database is corrupted "Bad" this should show up in the alert logs as an ORA-01578 error. I would search the archive log for the ORA- error and then compare that with the time stamp on the client error being reported. This is making the assumption of the definition of "Bad". It would be better to have the exact error messages posted.

Blanket tracing in the database is a tricky thing as it will tend to affect the performance of your entire application. And leaving it on for an entire week may not be feasible. I have also found in one case (cant remember the exact circumstance) where turning on tracing fixed the error.

One method I have used in the past is to add the sql statement to alter the session and turn on sqltrace. This is predicated on the ability to modify the code in some way. Depending on the application this may or may not be possible.

Another method would be to work with the DBA to identify the session and turn on sql trace for that session. Also if you can identify the offending sql statements and parameter values you may be able to replicate the problem outside the service.

I have found most ORM's avoid passing the ORA- error back. However it is typically logged in the application server layer with the associated ORM error.

I have used these method and variations of these method to trouble shoot errors in the application. I hope this is useful.