Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

We have Oracle 11g in production. Application system is still under active development. It will be very handy to get SQL statements which cause any error.

Does Oracle provide a standard function to trace and log these statements and additional (debug) info?

share|improve this question
add comment

1 Answer

While I'm not sure if it's the easiest way, you can use an "after servererror on database" trigger to log all errors to a table.

See http://nadvi.blogspot.se/2010/12/log-all-database-errors-to-table.html

See also http://psoug.org/reference/system_trigger.html

The documentation for system event triggers is here: http://docs.oracle.com/cd/E11882_01/appdev.112/e25519/create_trigger.htm#CIHHJGAJ

share|improve this answer
    
there area no SQL statment info. just error message and who ( v$session ) cause it. –  evg345 Apr 18 '13 at 8:11
    
i found DBMS_TRACE package to trace all sql statments and suppose to combine it with "after servererror on database" trigger –  evg345 Apr 18 '13 at 8:22
    
The PSOUG link has an example of how to capture the SQL statement the session was executing when the error occurred. DBMS_TRACE will only trace PL/SQL code so if you have a Java or .Net app for example then this won't help you. –  Colin 't Hart Apr 18 '13 at 8:28
    
to (Colin 't Hart). there are "Tracing SQL" section. –  evg345 Apr 18 '13 at 9:31
    
I understand that this will only trace SQL calls made from PL/SQL but please let me know if this is not the case. –  Colin 't Hart Apr 18 '13 at 9:34
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.