10035 event tracing no more records sql_texts to alert.log

Cursordump is the way to go.

I’ve moved my blog from https://insanedba.blogspot.com to https://dincosman.com Please update your bookmarks and follow/subscribe at the new address for all the latest updates and content. More up-to-date content of this post may be available there.

Parse failures are actually not stored in the data dictionary and therefore cannot be identified through querying the data dictionary.

As of Oracle 10g, event 10035 can be used to report all failed parses.  

With the 19.16 Release Update, Oracle does not record SQL statements to alert.log. Any statement that fails at the parsing stage is recorded with the error number and the process OSPID. Also, setting the hidden "_kks_parse_error_warning" parameter to 1 does not help report the failed SQL statements.

I tried changing the levels (1-128) of tracing but could not be successful in detecting the failed statements. 

It is recorded as below after 19.16 RU. SQL statement is no longer recorded in alert.log.

I have searched  x$kglob table, which is the primary library cache object underneath v$sql view. 

kglnaobj column of x$kglob table can be used to detect these failing SQL statements, but its data type is VARCHAR2(1000). For statements which are longer than 1000 characters, it is not possible to detect with this method.

The best way to go for detecting these statements is dumping the cursor with the below command and then viewing the tracefile(.trc) and  searching for the "sql=" statement. The tracefile name is typically formatted as instancename_ora_ospid.trc. For my example, it is bltdb1_ora_28682.trc. Also, tracefile information is available on v$process view.