Question

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.

Answer 1

You 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