problem with specifying HINT for SCRIPT for USERS

The following query is run when I click on SCRIPT tab of USERS

SELECT

o . object_type , pp . *

FROM dba_objects o ,

(SELECT

p. grantee ,

p. owner ,

p. table_name ,

NULL column_name ,

p.privilege,

p. grantable

FROM dba_tab_privs p, dba_users u

WHERE p. grantee = u. username

UNION ALL

SELECT

p. grantee ,

p. owner ,

p. table_name ,

p. column_name ,

p.privilege,

p. grantable

FROM dba_col_privs p, dba_users u

WHERE p. grantee = u. username ) pp

WHERE o . owner = pp . owner AND o . object_name = pp . table_name

AND o . object_type IN

( ‘TABLE’ ,

‘VIEW’ ,

‘SEQUENCE’ ,

‘PACKAGE’ ,

‘PROCEDURE’ ,

‘FUNCTION’ ,

‘SNAPSHOT’ ,

‘MATERIALIZED VIEW’ ,

‘DIRECTORY’ ,

‘LIBRARY’ ,

‘TYPE’ ,

‘OPERATOR’ ,

‘INDEXTYPE’ )

AND pp . grantee = ‘EROLLA’

ORDER BY pp . grantee , pp . owner , pp . table_name , pp . column_name

The only thing that seems to work is if there is onle one hint at the very top

SELECT /*+ USE_HASH(pp) */

o . object_type , pp . *

FROM dba_objects o ,

(SELECT

How do I specify this in OPTIONS=>Oracle=>optimizer_Hints?

Huh. Looks like you aren’t the first one to ask for this. We got a support
case years ago where someone strongly asked for the same thing. I didn’t
want to just put the hint in for everyone, so I made an undocumented Toad.ini
setting.

While Toad is not running, edit Toad.ini with notepad. Add this line in the
[SETTINGS] section

HashHintforPrivsQuery =1

That will add almost the same thing as what you asked. You’ll get:

SELECT /*+ USE_HASH(PP,O) / o.object_type , pp.

from dba_objects o,

……

Thank you I will try it. In the meantime I have another question. When I enter a
user name and press F4 and then click on the SCRIPT tab I have no way of
cancelling short of aborting Toad. But when I go to SB=>USERS and click on a
user and then click on SCRIPT I can cancel.

Thank you. That seems to work. Can that be incorporated as a check box on the
OPTIONS=>ORACLE=>HINTS page.

I don’t know, there are so many options already. I’m hesitant to
start a trend of adding checkboxes for one particular hint in one particular
query. Plus, I’m not sure I could clearly state what query it would be for
a few words next to a checkbox.

If you or your coworkers find queries run by Toad that perform badly, please let
us know and I’ll fix the ones I can.