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.