How can I see all errors on an instance?

[joeyb]

Friends!

I recently hooked up a 3rd-party application to one of my Oracle instances. I can see it successfully login to the instance (using JDBC) and it says it inserts a record successfully. I look on the database - the record is not there. I suspect that this software's error checking is not good and it is not detecting an Oracle error that is preventing the insert.

Is there a way to see all error messages generated by an instance for SQL-level commands? I can see the insert statement in v$sql, but it has bind variables, so I can't see the actual values passed.

Thanks in advance for any help / advice!!

Joe B.

[SANJAY_G]

check the alert log file in bdump directory. also check whether any trace file generated?

Sanjay

[agasimani]

Set trace in the session by:
DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSEION(id, id, TRUE);
ids can be obtained from v$session table. Once you insert the record and commit, you can check the trace file. But make sure you turn off the trace as soon as insert is committed or else you will endup having many unnecessary information.

[joeyb]

Thanks for the responses!

I should have mentioned that this 3rd party application connects to the database; does the insert and then disconnects. There's not really anyway that I can know the sid and serial# to SET_SQL_TRACE_IN_SESSION (or is there?).

Thanks again!

[sambavan]

Depending on the trace level 0 to 9, you can get the sessions information. 0 the lowest and 9 being the highest. If you get to set your trace level to be 10, it would be very detail, but would drastically reduce the performance.

Check the docs of technet on setting an sql trace.

Sam