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.