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.  

	Until explicitly disabled, it will track sql statements with parsing errors.
	SQL> alter system set events '10035 trace name context forever, level 1';
	Levels:
	level 1+ Print out user SQL statements
	level >3 Print out recursive SQL
	level >5 Print out row cache cached cursor stats
	Turning off :
	SQL> alter system set events '10035 trace name context off';

view raw 10035_event.txt hosted with ❤ by GitHub

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.

	2023-05-21T17:18:03.998348+03:00
	PARSE ERROR: ospid=28682, error=923 for statement:
	Additional information: hd=0x6ae210f0 phd=0x8bdc7d18 flg=0x28 cisid=0 sid=0 ciuid=0 uid=0 sqlid=d431n9ajscp24
	...Current username=SYS
	...Application: sqlplus@blt01.localdomain (TNS V1-V3) Action:

view raw parse_error_after_RU.txt hosted with ❤ by GitHub

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

	SQL> select kglhdpar, kglhdadr, kglobt03, kglnaobj from sys.x$kglob where kglobt03='d431n9ajscp24'
	KGLHDPAR KGLHDADR KGLOBT03 KGLNAOBJ
	---------------- ---------------- --------------- --------------------------------------------------
	000000008BDC7D18 000000006AE210F0 d431n9ajscp24 select 1923
	000000008BDC7D18 000000008BDC7D18 d431n9ajscp24 select 1923

view raw query_kglob.txt hosted with ❤ by GitHub

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.