Hard Parse Failures in TOAD for Oracle Application

Hi Toad Team;

You are doing an amazing job. TOAD for Oracle is by far the best application for developers and dbas.

I opened 10035 kernel service debugging in our production database to catch parse failures (such as wrong written sqls, missing grants) for our applications with below command.
But what i found was most of the failing SQL statements are related to TOAD for Oracle application.
SYS@xxxx1> alter system set events '10035 trace name context forever, level 1';
System altered.

Whenever a new connection is opened in TOAD for Oracle(16.1.53.1594) application. Below sql statements are recorded in alert.log. These sql statements are not getting executed, because they are failing at parsing stage.
Most of them is missing grants. They query mostly data dictionary. These ditionary views should not be queried for each new connection. Most of TOAD for Oracle users are developers not DBAs in our environment and SURELY They do not have dba role privileges.

In my humble opinion, below sql statements should be revised and LEAST PRIVILEGE principle should be followed.

I detected sql statements texts with the below query.
select * from sys.x$kglob where kglobt03='16z2kbbadhywf'

PARSE ERROR: ospid=383644, error=942 for statement:
Additional information: hd=0x33d6c44ce0 phd=0x32f1dce628 flg=0x20 cisid=178 sid=178 ciuid=178 uid=178 sqlid=16z2kbbadhywf
...Current username=XXXX
...Application: TOAD 16.1.53.1594 Action: 60447360,424440048,411629824
2023-04-04T16:02:02.555512+03:00
----- PL/SQL Call Stack -----
object line object
handle number name
SQL_STATEMENT : select 1 from DBA_SYNONYMS where rownum = 1

0x332f88eb58 187 anonymous block
PARSE ERROR: ospid=383644, error=942 for statement:
Additional information: hd=0x31e811a680 phd=0x3341e62338 flg=0x20 cisid=178 sid=178 ciuid=178 uid=178 sqlid=4rfa4b12rvgj6
...Current username=XXXX
...Application: TOAD 16.1.53.1594 Action: 60447360,424440048,411629824
2023-04-04T16:02:02.559179+03:00
----- PL/SQL Call Stack -----
object line object
handle number name

SQL_STATEMENT : select 1 from DBA_OBJECTS where rownum = 1

0x332f88eb58 195 anonymous block
PARSE ERROR: ospid=383644, error=942 for statement:
Additional information: hd=0x3239bd0d48 phd=0x3239bd0f78 flg=0x20 cisid=178 sid=178 ciuid=178 uid=178 sqlid=8x0ftdvrmpqjw
...Current username=XXXX
...Application: TOAD 16.1.53.1594 Action: 60447360,424440048,411629824
2023-04-04T16:02:02.559831+03:00
----- PL/SQL Call Stack -----
object line object
handle number name
0x332f88eb58 208 anonymous block

SQL_STATEMENT : Select 1
from toad.toad_restrictions where (user_name = USER or user_name in ( select ROLE from sys.session_roles))
and rownum = 1

PARSE ERROR: ospid=383644, error=942 for statement:
Additional information: hd=0x32fe12ad08 phd=0x32f95e1630 flg=0x20 cisid=178 sid=178 ciuid=178 uid=178 sqlid=g2v3uwv79a2ta
...Current username=XXXX
...Application: TOAD 16.1.53.1594 Action: 60447360,424440048,411629824
2023-04-04T16:02:02.561151+03:00
----- PL/SQL Call Stack -----
object line object
handle number name
0x332f88eb58 236 anonymous block

SQL_STATEMENT : select value from v$parameter where name = 'compatible'

PARSE ERROR: ospid=383644, error=942 for statement:
Additional information: hd=0x326641df10 phd=0x34bf471938 flg=0x20 cisid=178 sid=178 ciuid=178 uid=178 sqlid=0n5yq027a7uxk
...Current username=XXXX
...Application: TOAD 16.1.53.1594 Action: 60447360,424440048,411629824
2023-04-04T16:02:02.761288+03:00
----- PL/SQL Call Stack -----
object line object
handle number name
0x332f88eb58 399 anonymous block

SQL_STATEMENT : select value from v$parameter where name = 'max_string_size'

PARSE ERROR: ospid=383644, error=904 for statement:
Additional information: hd=0x33166e19c8 phd=0x3494956890 flg=0x20 cisid=178 sid=178 ciuid=178 uid=178 sqlid=4w19gxy4f0nx6
...Current username=XXXX
...Application: TOAD 16.1.53.1594 Action: 60447360,424440048,411629824
2023-04-04T16:02:02.817807+03:00
----- PL/SQL Call Stack -----
object line object
handle number name
0x332f88eb58 491 anonymous block

SQL_STATEMENT : SELECT SUBSTR (ut.version (), 1, 60) FROM DUAL

PARSE ERROR: ospid=383644, error=942 for statement:
Additional information: hd=0x33c6323990 phd=0x33c6323db8 flg=0x20 cisid=178 sid=178 ciuid=178 uid=178 sqlid=3m7nz2xxzggb4
...Current username=XXXX
...Application: TOAD 16.1.53.1594 Action: 60447360,424440048,411629824

SQL_STATEMENT : SELECT null FROM SYS.USER$ WHERE 0=1

PARSE ERROR: ospid=383644, error=942 for statement:
Additional information: hd=0x3353c5bee8 phd=0x334463d9d8 flg=0x20 cisid=178 sid=178 ciuid=178 uid=178 sqlid=06yvvb1zdu07p
...Current username=XXXX
...Application: TOAD 16.1.53.1594 Action: 60447360,424440048,411629824
2023-04-04T16:02:03.121513+03:00
----- PL/SQL Call Stack -----
object line object
handle number name
0x3491d01228 16 anonymous block

SQL_STATEMENT : SELECT 1 FROM QUEST_TEAM_CODING

PARSE ERROR: ospid=383644, error=942 for statement:
Additional information: hd=0x3339c6baa0 phd=0x341ee80928 flg=0x20 cisid=178 sid=178 ciuid=178 uid=178 sqlid=1qrcubraqssdb
...Current username=XXXX
...Application: TOAD 16.1.53.1594 Action: 60447360,424440048,411629824
2023-04-04T16:02:03.122161+03:00
----- PL/SQL Call Stack -----
object line object
handle number name
0x3491d01228 16 anonymous block

SQL_STATEMENT : SELECT 1 FROM DELL_COM_TEAM_CODING

PARSE ERROR: ospid=383644, error=942 for statement:
Additional information: hd=0x3261dbe540 phd=0x337a3f0670 flg=0x20 cisid=178 sid=178 ciuid=178 uid=178 sqlid=gua0wb1s92h59
...Current username=XXXX
...Application: TOAD 16.1.53.1594 Action: 60447360,424440048,4116298242023-04-04T16:02:03.122751+03:00
----- PL/SQL Call Stack -----
object line object
handle number name
0x3491d01228 16 anonymous block

SQL_STATEMENT : SELECT 1 FROM QUEST_COM_TEAM_CODING

PARSE ERROR: ospid=383644, error=942 for statement:
Additional information: hd=0x32f0063bc0 phd=0x33d635e140 flg=0x20 cisid=178 sid=178 ciuid=178 uid=178 sqlid=ccb2d10u0tf8n
...Current username=XXXX
...Application: TOAD 16.1.53.1594 Action: 60447360,424440048,411629824
2023-04-04T16:02:03.123330+03:00
----- PL/SQL Call Stack -----
object line object
handle number name
0x3491d01228 16 anonymous block

SQL_STATEMENT : SELECT 1 FROM SQLNAV4_VCS_CONFIG

2023-04-04T16:02:06.071831+03:00
PARSE ERROR: ospid=383644, error=6550 for statement:
Additional information: hd=0x33a4d83650 phd=0x33b792be30 flg=0x20 cisid=178 sid=178 ciuid=178 uid=178 sqlid=an8sw9vtpn1ct
...Current username=XXXX
...Application: TOAD 16.1.53.1594 Action: 60447360,424440048,411629824
2023-04-04T16:02:06.072125+03:00
----- PL/SQL Call Stack -----
object line object
handle number name
0x3329132570 30 anonymous block0x3329132570 57 anonymous block

PARSE ERROR: ospid=383644, error=942 for statement:
Additional information: hd=0x347aab1bc0 phd=0x31e9984af0 flg=0x20 cisid=178 sid=178 ciuid=178 uid=178 sqlid=ag12284rb44a3
...Current username=XXXX
...Application: TOAD 16.1.53.1594 Action: 60447360,424440048,411629824
2023-04-04T16:02:06.072808+03:00
----- PL/SQL Call Stack -----
object line object
handle number name
0x3329132570 67 anonymous block2023-04-04T16:02:54.856761+03:00

SQL_STATEMENT : SELECT 1 FROM plsql_profiler_data WHERE 0 = 1

  • This one needs DBMS_PROFILE package tables. It is an optional setup and not available in most database.

Hi Osman,

Thank you for the kind words.

Regarding the parse failures - yes, sometimes we use this technique to check for privileges, or to see if certain objects exist in the database. It is faster and easier than digging around in ALL_TAB_PRIVS, etc.

Some of those statements I recognize and they are indeed run (or just attempted to be parsed) by Toad. Some of them I do not recognize. (For instance, "select * from sys.x$kglob...". That one is not ours. We would not bother digging around in the X$ tables since only SYS can access them. Maybe it is a recursive statement that Oracle runs when we run something else.

Hi John,

You are right X$ table query is not related with TOAD. I used that query to find your sql_statements texts because i could not find them in gv$sql dynamic views as they are not parsed. I guess, There has been a misunderstanding.

select * from sys.x$kglob where kglobt03='16z2kbbadhywf'

As for the main topic, Do you plan a fix for dba_xxx views select statements? But also QUEST_COM_TEAM_CODING, SQLNAV4_VCS_CONFIG and DELL_COM_TEAM_CODING tables are queried. These tables do not exist.

Hi Osman,

We don't consider it a bug that Toad checks for existence of some of the DBA_ views in that way.

As for QUEST_COM_TEAM_CODING, SQLNAV4_VCS_CONFIG and DELL_COM_TEAM_CODING, these are tables that may be created by Toad or other Quest products. Toad needs to know if they exist so that Team Coding can work properly.

So we have no plans to change this behavior.

-John