Jun
17
Written by:
Bert
Wednesday, June 17, 2009 6:07 AM
Sometimes people will have a problem getting a TOAD feature to work – such as the PL/SQL debugger or profiler. For example a user might run TOAD and not be able to select or highlight the debugger icon on the main toolbar. So when they’re in the editor they cannot seemingly invoke or use the debugger. The initial conclusion is that TOAD must have a bug. But 90% of the time, it’s actually a database setup issue. Your DBA’s must grant TOAD users certain Oracle rights for some TOAD features to function. For example in both Oracle 10g and 11g, a TOAD user must have been granted the privilege DEBUG CONNECT SESSION for the debugger to function. We even have this specific information on the
ask toad wiki. Since this privilege is not part of any of the pre-canned roles for Oracle 7 backwards compatibility (i.e. CONNECT, RESOURCE, DBA), we get this very question all the time.
Furthermore, certain TOAD features might also require some Oracle supplied database objects within your database. For example, the debugger requires the DBMS_DEBUG package is installed and granted to TOAD users. Another good example is when asking TOAD to display an explain plan – there generally has to be a PLAN_TABLE table that is available. Finally some TOAD features require some TOAD specific database objects within your database. Note that there are relatively few of these, and you must choose to turn on or use those features before the objects must be within your database. But when either the Oracle supplied or TOAD specific database objects are not there, those TOAD features that require them are missing – thus again many might initially consider it to be a TOAD bug. It is not.
To help alleviate such issues as discussed above, TOAD offers a utility to make TOAD deployment and usage automatic and easy. But you have to manually run it – TOAD does not automatically do so for you (primarily because it just needs done once, so why check every time you run TOAD). This handy utility is called the Database Server Side Objects Wizard, shown below. Somebody with proper database rights should run this once before TOAD users start using the database – it helps you to configure your database to support whatever TOAD features you think you’ll be using.
Below are going to be 8 screen snapshots of running this wizard with a brief comment about each screen. It’s not very complicated, so I won’t belabor the obvious points 
The first screen is merely a welcome and identification screen where you pick what task you’re interested in performing – and which thus dictates what screen you’ll see next. In this example I’ll simply run through database object creation necessary for some TOAD features to work. Remember, this entire process is optional and you can optionally select only those items that your users will need. But when using a TOAD feature that has such a requirement, failure to run this wizard will generally prevent those TOAD features from working.
Second you simply need to provide a privileged account that can run the commands or script generated by this utility. Since you may need to create a TOAD user/schema to hold those TOAD specific database objects for some features (again, these are very few and optional), you’ll need to connect as someone who can create a user. That generally requires DBA type privileges, so here I’ve used the user SYSTEM (which I’d generally not do in a true production environment, instead I’d create my own DBA user accounts).
Third you simply choose to add, update or remove feature support for four key TOAD features: TOAD security, the profiler, team coding and Code Xpert. Since none of those database objects already exists, check boxes are disabled for remove, update, recompile, and administer. I simply chose to add support for the PL/SQL profiler – this is an Oracle specific set of database objects required for the TOAD PL/SQL profiler to work.
Fourth you choose to add or remove and grant the privileges necessary to use the TOAD space manager – which can track table space and data file growth and IO workloads over time, plus provide predictions about future growth. This feature is available on the Table Spaces screen via the last two tabs (Space History and IO History).
Fifth you decide on which method by which you want the wizard to create the TOAD schema or user. Specifically, by which collection of grants do you want it to obtain the required special privileges. Note that is only required when adding profiler support.
Sixth you simply provide the basic user creation information required to create the new TOAD user or schema.
Seventh you simply choose either to run the generated script as the privileged user that you provided on the second screen or to save it to a file. You can preview the script in the text window. There is a progress bar that updates during execution.
Eighth and finally, you simply review the object status. Basically you’re looking to see “Feature X is currently installed correctly” across the board. That’s it.
