is there any way to log all failed sql statements in oracle 10g to a table or file?
By failed I mean bad formated sql statement or sql statements that do not have permission for a table or object.
See Question&Answers more detail:osis there any way to log all failed sql statements in oracle 10g to a table or file?
By failed I mean bad formated sql statement or sql statements that do not have permission for a table or object.
See Question&Answers more detail:osYou may want to use Auditing like:
AUDIT SELECT TABLE, INSERT TABLE, DELETE TABLE, EXECUTE PROCEDURE BY ACCESS WHENEVER NOT SUCCESSFUL;
By ACCESS is for each statement (which seems like what you want). By SESSION would record one record per session (high volume environment).
Oracle's built in auditing has less overhead then a trigger. A trigger, which other answers contain, allows you to log the exact information you want. Auditing will also only catch hits on existing objects. If someone selects on a non-existent table (misspelled or whatnot) auditing will not catch it. The triggers above will.
A lot more info in the security guide: http://download.oracle.com/docs/cd/B19306_01/network.102/b14266/auditing.htm#i1011984