I recently learned from Jonathan Lewis’s blog that in version 12.2 details of invalid SQL statements are written to the alert log (after every 100 occurrences for a particular SQL statement).
Its worth to mention that similar functionality can be achieved in earlier versions by setting event 10035 (not to be confused with it’s more (in)famous sibling, event 10053).
I will demonstrate with version 12.1 using the same invalid SQL from Jonathan’s post (I have tested that this works at least as far back as 10.2):
Oracle Database 12c Enterprise Edition Release 18.104.22.168.0 - 64bit Production With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options SQL> ALTER SESSION SET EVENTS='10035 trace name context forever, level 1'; Session altered. SQL> select count(*) frm dual; Error starting at line : 1 in command - select count(*) frm dual Error at Command Line : 1 Column : 21 Error report - SQL Error: ORA-00923: FROM keyword not found where expected 00923. 00000 - "FROM keyword not found where expected" *Cause: *Action:
The following information is written in the alert log:
PARSE ERROR: ospid=14737, error=923 for statement: Sun Oct 08 01:14:43 2017 select count(*) frm dual Additional information: hd=0x6bffaa18 phd=0x71c88e18 flg=0x28 cisid=8 sid=8 ciuid=8 uid=8 PARSE ERROR: ospid=14737, error=923 for statement: Sun Oct 08 01:14:43 2017 select count(*) frm dual Additional information: hd=0x6bffaa18 phd=0x71c88e18 flg=0x28 cisid=8 sid=8 ciuid=8 uid=8 Sun Oct 08 01:14:43 2017 ----- PL/SQL Call Stack ----- object line object handle number name 0x6f9e6a10 1120 package body SYS.DBMS_SQL 0x727c2ba8 15 anonymous block
Due to the extra overhead this would add to the parse of every invalid SQL, I would suggest to only set this event temporarily at session or system level after identifying that this is a problem from the statistic “parse count (failures)”.
I just found out that Tanel covered this back in 2009 as part of an deep-dive article into diagnostic events, that is well worth a read.