While connected as sys to a Oracle database and using the schema browser under Toad for ORacle12.6.0.53 I tried to generate a script of a user and nothing happens. If you highlight a user in the schema browser and look at the script tab on the right nothing appears. This all worked in Oracle for Toad 12.1.1… I also found this stopped functioning in 12.5…
Are the scripts still being created for other object types? If so, maybe there is some SQL error happening in the background. Turn on spool SQL to see, and paste the output here.
If other object types are broken too, try shutting down Toad and then look in your User Files folder for a file called ToadActions.dat. Rename it to ToadActions_backup or something, and then try the script again in Toad. I haven’t seen it much in recent versions, but in older versions, if this file got corrupt, it would prevent certain things in Toad from working.
Session: SYS@CMSDEV_DBS
Timestamp: 16:30:04.981
Select * from DBA_users u
where U.USERNAME = :OneObjectName
order by username
:OneObjectName(VARCHAR[7],IN)=‘ABISWAS’
Session: SYS@CMSDEV_DBS
Timestamp: 16:30:05.008
select name,
case
when spare4 is not null and password is not null then spare4 || ‘;’ || password
when spare4 is not null then spare4
else password
end password
from sys.user$
where type# = 1
and NAME = :OneObjectName
:OneObjectName(VARCHAR[7],IN)=‘ABISWAS’
Session: SYS@CMSDEV_DBS
Timestamp: 16:30:05.015
Select o.object_type, pp.*
from dba_objects o,
(select owner, privilege, table_name, null column_name, grantee, grantable
from sys.DBA_TAB_PRIVS
where owner = :OneObjectName
UNION ALL
select owner, privilege, table_name, column_name, grantee, grantable
from sys.DBA_COL_PRIVS
where owner = :OneObjectName
) pp
where o.owner = pp.owner
and o.object_name = pp.table_name
and o.object_type in (‘TABLE’, ‘VIEW’, ‘SEQUENCE’, ‘PACKAGE’, ‘PROCEDURE’, ‘FUNCTION’, ‘DIRECTORY’, ‘LIBRARY’, ‘TYPE’, ‘OPERATOR’, ‘INDEXTYPE’, ‘EDITION’, ‘JOB’, ‘PROGRAM’, ‘SCHEDULE’)
order by pp.owner, pp.table_name, pp.grantee, pp.grantable, pp.privilege
:OneObjectName(VARCHAR[7],IN)=‘ABISWAS’
Other objects in schema browser seem to work ok. Only users seem to be failing.
Oh, I see what’s going on. You have your script tab set to show you the privileges on that user’s object to other users.
To switch it to show the script to create the user, click the first toolbar button on the script tab. Then, in the dialog that appears, click on the “Users” tab, and uncheck “Grants on the user’s objects to other users/roles”.
The “create user” and “grants on user’s objects” scripts don’t make sense to show together because if the script creates the user, then the user won’t have any objects to grant yet.
Hi John,
Thanks . this worked fine perfect.