Tuesday, April 17, 2012

Toad hangs when retrieving User Script 

When using the Schema Browser, user selects select a User and the User Script tab. Toad hangs (or taking a long time) on a 10g db. When user uses 9.2.0.5 database, User script works as it should.

Does not appear to matter which user is selected . Toad does not return script and it hangs. Changing the Optimize hint does not address issue.

Also, right-clicking a user then clicking Create Script does not work no matter to File, to Screen, or to Clipboard.

Cause
Toad for Oracle needs a script change.

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', 'EDITION', 'JOB', 'PROGRAM',
           'SCHEDULE')
      AND pp.grantee = 'PRODDTA'
    ORDER BY pp.grantee, pp.owner, pp.table_name, pp.column_name

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

HashHintforPrivsQuery =1

No comments:

Post a Comment