The SQL Editor is the original development area of TOAD. This window enables you to type, save, run, and tune SQL statements. In addition, you will learn how to use TOAD to create and execute SQL scripts, save the output, and examine the explain plan. This chapter will discuss and illustrate every option available in the SQL Editor.
User-Defined Shortcuts
TOAD is completely configurable. You can easily add your own shortcuts to TOAD. It is easy to change the shortcut keystrokes for existing shortcuts, and it is just as easy to add your own shortcuts.
Access the Editor Options menu with a right-click or by pressing the F10 key and selecting Editing Options.
To change an existing keystroke assignment, select Key Assignments, locate the particular assignment to change, and click on the Edit Sequence button
Using Variables
TOAD supports all kinds of SQL, from all kinds of applications. If you were to bring in SQL, for example, from a SQL*Forms application, it will contain bind variables. Bind variables are used to supply SQL with data at execution time. This allows applications to use the same SQL statement to select and manipulate different data, depending on the data supplied to the bind variables.
Using the same SQL statement makes efficient use of the Oracle RDBMS SQL pool as the SQL will not be reparsed when using bind variables. The text of the SQL remains the same, so Oracle will reuse the same execution plan, making for a better-performing database environment.
When TOAD encounters bind variables, it will prompt you for their value
Thursday, December 20, 2007
Using TOAD's Schema Browser
Whether you're a DBA or a developer, TOAD's Schema Browser is a powerful and functional interface for exploring all your database objects. Not only does the Schema Browser enable you to quickly and easily navigate the complex structures within the database, but it also enables you to both manage and control all those structures (where your granted Oracle privileges permit). This screen is so extremely useful that when you're not coding or debugging, you'll most likely be exploring your database with the TOAD Schema Browser. It is accessible from the main toolbar and from the main menu at Database, Schema Browser.
Although many of today's Windows tools utilize tree views for their explorer GUI design, TOAD has pioneered and adopted a tabbed GUI design. The problem with tree views is that you end up scrolling too much when lots of information must be displayed. For example, opening a tree-view node for a user's tables might well display dozens of nodes and cause the tree view to scroll numerous other items of interest out of the main viewing area. By contrast, with the tabbed approach, less scrolling is generally required. Returning to the prior example, choosing a user's tables from the Tables tab does not cause your other main object categories (that is, tabs) to scroll off anywhere. Although it may initially take some getting used to, TOAD's tabbed GUI design is infinitely more productive in terms of wasted scrolling efforts. And time is money.
Finally, you might think you can skip this chapter because the Schema Browser is just another explorer and people use those every day. But the TOAD Schema Browser has so many features and capabilities you might never find that skipping this one chapter could make your overall TOAD usage much less productive. In fact, during the TOAD User Groups when "Tips and Tricks" are discussed, this one area alone seems to generate the most comments like "I did not know TOAD could do that." So please read on to learn how to fully utilize TOAD's Schema Browser and all its advanced capabilities.
Making Schema Browser Your Startup Screen
Many people find the Schema Browser so useful that they want TOAD to bring it up as their initial screen when TOAD either launches or creates a new database connection. The concept is that often you must navigate to something of interest and then perform work on it. Thus using the Schema Browser to locate and then operate on that object is often the most productive method for working within TOAD. To accomplish this, you simply check the box for Browser under the group Startup Windows per Connection under the StartUp category in the TOAD Options screen, as shown in Figure 2.1. The TOAD Options screen is accessible from the main menu at View, Options. Now TOAD will open a Schema Browser when you first launch it and for each new database connection.
Enabling or Disabling Schema Browser Tabs
Most people do not work with every conceivable Oracle object type, but rather a select subset that fits their needs and job description. For example, a PL/SQL developer may not care to see tabs for tablespaces, rollback segments, profiles, policies, and other more DBA-relevant information. So you can simply right-click to open a context menu on any tab or a tab's toolbar and select which tabs to enable or disable for display on the Schema Browser Then you will have a Schema Browser whose displayed tabs fit your needs. This might be quite useful for people who want the tabs to span lines, but cannot accept having all the tabs being displayed and thus losing so much real estate for displaying each tab's contextual information.
Filtering Schema Browser Schemas
It's not unusual for a typical database to have lots of schemas. In fact, many of today's database applications routinely create a schema per application user. These user schemas often possess no database objects, but instead access the central application tables by using grants and synonyms. When you're working within TOAD's Schema Browser, these user schemas are really of limited value. Thus you would like to see them filtered out of the drop-down list in the Schema Browser for the schema to focus upon (because they have no objects and will never really display anything). As with everywhere else in TOAD, there is an option to control this and it's simple to do.
Filtering Schema Browser Objects
It's not unusual for a typical database schema to have lots of objects. For example, Enterprise Resource Planning (ERP) applications such as SAP, Peoplesoft, Siebel, and Oracle Apps can have thousands of tables. As such, they often follow some naming convention to ease the object name overload. So the general ledger table names may be prefixed with "GL_", the accounts receivable with "AR_", the accounts payable with "AP_", and so on. But as the DBA, you or the developer might not be interested in always seeing all possible choices, but instead merely a user-defined subset based on the naming standard. TOAD provides filters for most of the Schema Browser tabs for accomplishing exactly this task.
Using Favorites as a Custom Schema Browser
Often in TOAD, you need to work with the same group or groups of objects. For example, you might only need to routinely work with the production tables for CUSTOMER and EMPLOYEE, their indexes and views. So you would like to have a customized Schema Browser for just those objects. TOAD offers the Favorites tab so that you can easily and quickly work in this manner. It can be a huge productivity enhancer. But far too often people don't seem to know about this feature's existence, and thus are working harder than they really need to. You merely need to set it up, define your favorites, and then begin using it as your customized schema browser.
Setting Some Advanced Schema Browser Options
TOAD's Schema Browser is a highly complex bit of code with numerous capabilities and nearly endless options. Although some of these options have already been examined within this chapter, there are still many more. In fact, there are so many options (and growing) that the TOAD Options category for the Schema Browser now has two complete pages. Unfortunately the options on each page do not seem to be based on any logical grouping, but rather just an overflow of what could fit on one page. So you'll need to visit both pages to be certain you've chosen all of the right option settings.
Although many of today's Windows tools utilize tree views for their explorer GUI design, TOAD has pioneered and adopted a tabbed GUI design. The problem with tree views is that you end up scrolling too much when lots of information must be displayed. For example, opening a tree-view node for a user's tables might well display dozens of nodes and cause the tree view to scroll numerous other items of interest out of the main viewing area. By contrast, with the tabbed approach, less scrolling is generally required. Returning to the prior example, choosing a user's tables from the Tables tab does not cause your other main object categories (that is, tabs) to scroll off anywhere. Although it may initially take some getting used to, TOAD's tabbed GUI design is infinitely more productive in terms of wasted scrolling efforts. And time is money.
Finally, you might think you can skip this chapter because the Schema Browser is just another explorer and people use those every day. But the TOAD Schema Browser has so many features and capabilities you might never find that skipping this one chapter could make your overall TOAD usage much less productive. In fact, during the TOAD User Groups when "Tips and Tricks" are discussed, this one area alone seems to generate the most comments like "I did not know TOAD could do that." So please read on to learn how to fully utilize TOAD's Schema Browser and all its advanced capabilities.
Making Schema Browser Your Startup Screen
Many people find the Schema Browser so useful that they want TOAD to bring it up as their initial screen when TOAD either launches or creates a new database connection. The concept is that often you must navigate to something of interest and then perform work on it. Thus using the Schema Browser to locate and then operate on that object is often the most productive method for working within TOAD. To accomplish this, you simply check the box for Browser under the group Startup Windows per Connection under the StartUp category in the TOAD Options screen, as shown in Figure 2.1. The TOAD Options screen is accessible from the main menu at View, Options. Now TOAD will open a Schema Browser when you first launch it and for each new database connection.
Enabling or Disabling Schema Browser Tabs
Most people do not work with every conceivable Oracle object type, but rather a select subset that fits their needs and job description. For example, a PL/SQL developer may not care to see tabs for tablespaces, rollback segments, profiles, policies, and other more DBA-relevant information. So you can simply right-click to open a context menu on any tab or a tab's toolbar and select which tabs to enable or disable for display on the Schema Browser Then you will have a Schema Browser whose displayed tabs fit your needs. This might be quite useful for people who want the tabs to span lines, but cannot accept having all the tabs being displayed and thus losing so much real estate for displaying each tab's contextual information.
Filtering Schema Browser Schemas
It's not unusual for a typical database to have lots of schemas. In fact, many of today's database applications routinely create a schema per application user. These user schemas often possess no database objects, but instead access the central application tables by using grants and synonyms. When you're working within TOAD's Schema Browser, these user schemas are really of limited value. Thus you would like to see them filtered out of the drop-down list in the Schema Browser for the schema to focus upon (because they have no objects and will never really display anything). As with everywhere else in TOAD, there is an option to control this and it's simple to do.
Filtering Schema Browser Objects
It's not unusual for a typical database schema to have lots of objects. For example, Enterprise Resource Planning (ERP) applications such as SAP, Peoplesoft, Siebel, and Oracle Apps can have thousands of tables. As such, they often follow some naming convention to ease the object name overload. So the general ledger table names may be prefixed with "GL_", the accounts receivable with "AR_", the accounts payable with "AP_", and so on. But as the DBA, you or the developer might not be interested in always seeing all possible choices, but instead merely a user-defined subset based on the naming standard. TOAD provides filters for most of the Schema Browser tabs for accomplishing exactly this task.
Using Favorites as a Custom Schema Browser
Often in TOAD, you need to work with the same group or groups of objects. For example, you might only need to routinely work with the production tables for CUSTOMER and EMPLOYEE, their indexes and views. So you would like to have a customized Schema Browser for just those objects. TOAD offers the Favorites tab so that you can easily and quickly work in this manner. It can be a huge productivity enhancer. But far too often people don't seem to know about this feature's existence, and thus are working harder than they really need to. You merely need to set it up, define your favorites, and then begin using it as your customized schema browser.
Setting Some Advanced Schema Browser Options
TOAD's Schema Browser is a highly complex bit of code with numerous capabilities and nearly endless options. Although some of these options have already been examined within this chapter, there are still many more. In fact, there are so many options (and growing) that the TOAD Options category for the Schema Browser now has two complete pages. Unfortunately the options on each page do not seem to be based on any logical grouping, but rather just an overflow of what could fit on one page. So you'll need to visit both pages to be certain you've chosen all of the right option settings.
Setting Up TOAD for Successful Use
Meeting TOAD's Database Connectivity Needs
TOAD must be able to communicate with your Oracle database. That means TOAD must be able to both connect and then work with your database whether it's local (on PC) or remote (on server). This requires that the Oracle network client libraries exist on your PC for TOAD to call. Of course, Oracle in its infinite wisdom has renamed the client software and libraries as shown in Table 1.1.
Table 1.1. Oracle Connectivity Version
Terminology
Client Library
Oracle 7
SQL*Net
ORA7x.DLL
Oracle 8
Net8
ORACLIENT8.DLL
Oracle 8i
Net8
ORACLIENT8.DLL
Oracle 9i
Oracle Net
ORACLIENT9.DLL
Oracle 9iR2
Oracle Net
ORACLIENT9.DLL
Furthermore, you must also verify that your PC's environment variable for PATH includes the directory where those network client libraries are installed (that is, the Oracle installation directory). Of course, this too has changed over the Oracle versions and can also be modified by the person doing the install. Here are some examples of locations where they might be found:
C:\ORAWIN\BIN
C:\ORAWIN95\BIN
C:\ORANT\BIN
C:\ORACLE\BIN
C:\ORACLE\ORA92\BIN
Failure to install and reference these client libraries will prevent TOAD from functioning. However, you must also keep in mind your version of the client libraries versus the database you are talking to. Having client libraries from an older version of Oracle but talking to newer versions of Oracle is a recipe for disaster. You may encounter Oracle OCI API error messages from within TOAD. This might occur as TOAD attempts to call an OCI function for some new features or capability of the newer database version that the older network client library does not support.
For example, using an 8i client with a 9i database and attempting to work with columns whose data type is XMLTYPE will yield the cryptic message: OCI-21500: internal error code, arguments: [kocgpn129], [2], [], [], [], [], [], []. This is not a TOAD bug or lack of TOAD support for new features, but a simple user error of trying to use old or incompatible network client libraries with newer versions of the database. The best advice is to always use the latest and greatest network client, as it is always backward-compatible with prior database versions.
Understanding TOAD's Oracle-Based Security
Probably the most frequently asked question by DBAs new to TOAD is: "Will TOAD permit my developers to do things that they should not?" The simple answer is definitely not, because TOAD cannot override or supersede Oracle's security. TOAD users have only whatever roles, system privileges, or object grants that exist for them within the database. Thus they can do no more in TOAD than they could in SQL*Plus (they just can do it more easily and faster). To reiterate, TOAD only permits users to do whatever the DBA has granted them—there are no loopholes or exceptions.
But this does require the DBAs managing the Oracle schemas (that is, users) to have a very firm grasp on all the privileges they are actually handing out. For example, far too many DBAs still grant the predefined roles CONNECT, RESOURCE, and DBA to their users—even though Oracle states that these roles are there merely for backward compatibility and that you should create and grant your own customized roles. Unfortunately, many people seem to have missed this fact and still overuse the predefined roles. But some DBAs do not fully realize which system privileges the predefined roles grant. So granting a schema CONNECT means that the user can create clusters, database links, sequences, synonyms, tables, and views via TOAD, because those are the privileges that CONNECT possesses. So know your predefined roles well if you're going to use them. However, the recommendation is to create your own custom roles and grant those to your DBAs and developers,
-- Role: Junior Developer
-- Trusted to do some things
CREATE ROLE DEVELOPER_JR NOT IDENTIFIED;
--
-- Obviously required priv's
GRANT CREATE SESSION TO DEVELOPER_JR;
GRANT ALTER SESSION TO DEVELOPER_JR;
GRANT ALTER USER TO DEVELOPER_JR;
--
-- Junior Developer Priv's
GRANT CREATE PROCEDURE TO DEVELOPER_JR;
GRANT CREATE SEQUENCE TO DEVELOPER_JR;
GRANT CREATE SYNONYM TO DEVELOPER_JR;
GRANT CREATE TRIGGER TO DEVELOPER_JR;
GRANT CREATE TYPE TO DEVELOPER_JR;
GRANT CREATE VIEW TO DEVELOPER_JR;
-- Role: Senior Developer
-- Trusted to do most things
CREATE ROLE DEVELOPER_SR NOT IDENTIFIED;
--
-- Inherit All Junior Priv's
GRANT DEVELOPER_JR TO DEVELOPER_SR;
--
-- Senior Developer Priv's
GRANT CREATE DATABASE LINK TO DEVELOPER_SR;
GRANT CREATE DIMENSION TO DEVELOPER_SR;
GRANT CREATE INDEXTYPE TO DEVELOPER_JR;
GRANT CREATE LIBRARY TO DEVELOPER_SR;
GRANT CREATE MATERIALIZED VIEW TO DEVELOPER_SR;
GRANT CREATE OPERATOR TO DEVELOPER_JR;
GRANT CREATE TABLE TO DEVELOPER_SR;
Another area of possible security oversight is to forget the PUBLIC schema and its granted roles, system privileges, or object grants. For example, granting SELECT ANY TABLE to PUBLIC (which is generally not advisable) means that TOAD users can see the entire database's table data. So manage PUBLIC wisely. But do note that TOAD uses the data dictionary views for ALL_xxx and DBA_xxx that have been granted to PUBLIC. And don't worry about the DBA_xxx views; again, TOAD only allows users to see those based upon Oracle security. Thus a user must have SELECT ANY TABLE, SELECT ANY DICTIONARY, or SELECT_CATALOG_ROLE (depending on Oracle version and O7_DICTIONARY_ACCESSIBILITY setting) in order for TOAD to reference the DBA_xxx views. Remember that as you create roles for users who will be using TOAD's DBA module. For those users, one of the following security scenarios should be adopted:
O7_DICTIONARY_ACCESSIBILITY = true
O7_DICTIONARY_ACCESSIBILITY = true, SELECT ANY DICTIONARY granted, SELECT ANY TABLE not granted (second best recommendation)
O7_DICTIONARY_ACCESSIBILITY = true, SELECT ANY DICTIONARY granted, SELECT ANY TABLE granted
O7_DICTIONARY_ACCESSIBILITY = true, SELECT ANY DICTIONARY not granted, SELECT ANY TABLE not granted, SELECT_CATALOG_ROLE granted (best recommendation)
Creating the All-Important TOAD Schema
Strictly speaking, you do not have to create any database server-side objects in order to use TOAD. You can simply install TOAD on your PC and go on your merry way. But there are screens where a developer will require access to an Oracle explain plan table. For example, the SQL Editor window has a tab for displaying the explain plan for the current SQL statement. Thus TOAD will need access to a plan table in order to process and then display the resulting explain plan. You have three options here.
First, sometimes DBAs prefer to create a DBA schema-owned, general-purpose and shared explain plan table using Oracle's scripts. So the steps to implement might look something like this:
Connect as SYSTEM (or other DBA account).
Run Oracle's RDBMS/ADMIN/UTLXPLAN.SQL.
GRANT ALL ON SYSTEM.PLAN_TABLE TO PUBLIC
CREATE PUBLIC SYNONYM PLAN_TABLE FOR SYSTEM.PLAN_TABLE
TOAD can work with such a setup. You should merely set the TOAD options for the Explain Plan Table Name field under the Oracle category to PLAN_TABLE (meaning the public synonym for the general-purpose table set up by the DBA).
There are two caveats with this method. First, you must make sure the DBA schema-owned explain plan table is current for the Oracle version being used. It's really quite easy to forget to update this table with major Oracle upgrades. This can cause TOAD to encounter problems with missing columns (that is, a call to Oracle will try to populate newer columns that don't exist in the old plan table structure). And second, TOAD will not be able to save and recall plans if you use this method because TOAD requires its own plan table to support such operations. You must instead use one of the two remaining methods.
Second (and recommended), you can also create a special TOAD schema to own a general-purpose and shared explain plan table using TOAD's TOADPREP.SQL script (found in the TEMPS subdirectory of the TOAD install directory). TOADPREP.SQL first creates the TOAD schema, and then creates its required explain plan objects. To accomplish this implementation, the steps are as follows:
Edit TOAD's TEMPS/TOADPREP.SQL.
Connect as SYSTEM (or other DBA account).
Run TOAD's TEMPS/TOADPREP.SQL.
TOAD will now be able to support the save and recall of explain plans, as long as you remember to check Save Previous Explain Plan Results in the TOAD Options screen under the Oracle category, as shown in Figure 1.2.
The TOADPREP.SQL script has changed significantly with version 7.4. Although the script has always created both the TOAD schema and all its explain-plan required objects, it has been updated such that you only need to modify the first three DEFINE statements in the script in order to control the script's behavior. It used to be that you had to review the entire SQL script for possible changes. So the new TOADPREP.SQL script shown in Listing 1.1 is much easer to work with.
Listing 1.1 TOADPREP.SQL Script
REM This script will create TOAD objects in their own
REM schema. If you DO NOT want to create a unique system
REM schema for TOAD objects, load the file NOTOAD.SQL
REM
REM Otherwise, start a new Oracle connection as SYSTEM ( or
REM any other user with privileges to create a new USER)
REM and, while connected as that user, execute the following
REM by clicking the third toolbar button in a SQL Edit OR
REM by selecting the menu option "SQL_Window/Execute as Script"
REM
REM Ver Date Description
REM === ========== =======================================
REM 1.1 10/06/1999 1. Added STORAGE clauses to the table
REM create commands so that not too much
REM disk space will be allocated.
REM 2. Removed obsolete TOAD_TEMP and
REM TOAD_DEP_TEMP.
REM 1.2 11/17/1999 1. Changed index on TOAD_PLAN_TABLE from
REM unique to non-unique.
REM 1.3 05/23/2001 1. Added partition-related columns and
REM DISTRIBUTION to TOAD_PLAN_TABLE
REM 1.4 01/18/2001 1. Added OBJECTNAME function
REM 1.5 03/29/2002 Added EXECUTE ANY PROCEDURE
REM 1.6 03/31/2002 Reworked entire script so user custimizations
REM can be made via DEFINE variables in one place
REM --------- Make all changes right here --------------------
REM --------- Do not change the name of the TOAD user --------
DEFINE UPW_TOAD=TOAD
DEFINE DEF_TSPACE=USER_DATA
DEFINE TMP_TSPACE=TEMPORARY
REM ------------------ Create the TOAD User ------------------
DROP USER TOAD CASCADE;
CREATE USER TOAD
IDENTIFIED BY &UPW_TOAD
DEFAULT TABLESPACE &DEF_TSPACE
TEMPORARY TABLESPACE &TMP_TSPACE
QUOTA UNLIMITED ON &DEF_TSPACE
QUOTA 0K ON SYSTEM;
GRANT CONNECT TO TOAD;
GRANT RESOURCE TO TOAD;
GRANT CREATE PUBLIC SYNONYM TO TOAD;
--The following grant is only necessary if you intend to install the TOAD
--Profiler objects into the TOAD schema
GRANT EXECUTE ANY PROCEDURE TO TOAD;
REM ----------------- Drop public synonyms --------------------
DROP PUBLIC SYNONYM TOAD_PLAN_SQL;
DROP PUBLIC SYNONYM TOAD_PLAN_TABLE;
DROP PUBLIC SYNONYM TOAD_SEQ;
REM ----------------- Connect as TOAD -------------------------
CONNECT TOAD/&UPW_TOAD;
REM ----------------- Create the Explain Plan objects
DROP TABLE TOAD.TOAD_PLAN_SQL;
DROP TABLE TOAD.TOAD_PLAN_TABLE;
DROP SEQUENCE TOAD.TOAD_SEQ;
CREATE TABLE TOAD.TOAD_PLAN_SQL (
USERNAME VARCHAR2(30),
STATEMENT_ID VARCHAR2(32),
TIMESTAMP DATE,
STATEMENT VARCHAR2(2000) )
STORAGE (INITIAL 40K NEXT 24K);
CREATE UNIQUE INDEX TOAD.TPSQL_IDX ON
TOAD.TOAD_PLAN_SQL ( STATEMENT_ID );
CREATE TABLE TOAD.TOAD_PLAN_TABLE (
STATEMENT_ID VARCHAR2(32),
TIMESTAMP DATE,
REMARKS VARCHAR2(80),
OPERATION VARCHAR2(30),
OPTIONS VARCHAR2(30),
OBJECT_NODE VARCHAR2(128),
OBJECT_OWNER VARCHAR2(30),
OBJECT_NAME VARCHAR2(30),
OBJECT_INSTANCE NUMBER,
OBJECT_TYPE VARCHAR2(30),
SEARCH_COLUMNS NUMBER,
ID NUMBER,
COST NUMBER,
PARENT_ID NUMBER,
POSITION NUMBER,
CARDINALITY NUMBER,
OPTIMIZER VARCHAR2(255),
BYTES NUMBER,
OTHER_TAG VARCHAR2(255),
PARTITION_ID NUMBER,
PARTITION_START VARCHAR2(255),
PARTITION_STOP VARCHAR2(255),
DISTRIBUTION VARCHAR2(30),
OTHER LONG)
STORAGE(INITIAL 80K NEXT 36K) ;
CREATE INDEX TOAD.TPTBL_IDX ON
TOAD.TOAD_PLAN_TABLE ( STATEMENT_ID );
CREATE SEQUENCE TOAD.TOAD_SEQ START WITH 1 CACHE 20;
CREATE PUBLIC SYNONYM TOAD_PLAN_SQL FOR TOAD.TOAD_PLAN_SQL;
CREATE PUBLIC SYNONYM TOAD_PLAN_TABLE FOR TOAD.TOAD_PLAN_TABLE;
CREATE PUBLIC SYNONYM TOAD_SEQ FOR TOAD.TOAD_SEQ;
GRANT SELECT, INSERT, UPDATE, DELETE ON TOAD.TOAD_PLAN_SQL TO PUBLIC;
GRANT SELECT, INSERT, UPDATE, DELETE ON TOAD.TOAD_PLAN_TABLE TO PUBLIC;
GRANT SELECT, ALTER ON TOAD.TOAD_SEQ TO PUBLIC;
REM -------- Create the ObjectName function for use in Kill/Trace -----------
DROP FUNCTION TOAD.MYOBJECTNAME;
CREATE OR REPLACE function TOAD.ObjectName(in_object_id in number) return
varchar
is
return_string varchar2(100);
begin
select OWNER||'.'||OBJECT_NAME
into return_string
from all_objects
where object_id = in_object_id;
return return_string;
end ObjectName;
/
GRANT EXECUTE ON TOAD.OBJECTNAME TO PUBLIC;
Third and finally, you can also create a private TOAD explain plan table and all its required objects per TOAD user via the NOTOAD.SQL script (also found in the TEMPS subdirectory of the TOAD install directory). To accomplish this implementation, the steps are as follows:
Connect as each and every TOAD user.
Run TOAD's TEMPS/NOTOAD.SQL.
This method is supported for backward compatibility, but it's very hard to imagine a case where you would want to choose this implementation option. This method would require every TOAD user to have CREATE TABLE privilege, CREATE SEQUENCE privilege, and some tablespace quota on at least one tablespace. Plus it would result in numerous additional database objects as a whole (that is, two tables, two indexes, and one sequence created per TOAD user). But the option does exist for those who want to use it. The only advantage to this approach is that, like the prior method, it too supports the save and recall of explain plans. Because this method is not recommended, the NOTOAD.SQL script is not shown.
Activating TOAD's "Read-Only" Mode
Most people don't realize that TOAD comes with two modes of operation entirely under their control. TOAD can operate in either read/write or read-only mode, simply controlled by the license files in the TOAD install directory. When the TOAD.LIC file is a copy of the FULLTOAD.LIC file (default), TOAD operates in read/write mode. And when the TOAD.LIC file is a copy of the READONLY.LIC file, TOAD operates in read-only mode. Note that read-only refers to the fact that users cannot save or commit anything to the database. However, read-only users are still permitted to create, modify, and save other files on the local PC. They cannot do anything but read database data.
Note that this was the original method by which TOAD supported a read-only mode of operation. Beginning in version 7.3, TOAD's entirely new advanced security offering (see the following section) offers a much cleaner and simpler method for making TOAD read-only.
Using TOAD's New Advanced Security
Beginning with version 7.3, TOAD offers an entirely new and completely customizable security mechanism. With it, you can very easily define what features or functions should work in TOAD by either database user or role. For example, you can take the DEVELOPER_JR database role from the prior section and further control what a junior developer can do within TOAD as shown in Figure 1.3. This screen is callable from the main menu at Tools, Toad Security. However, to use this screen, you must first have a TOAD schema (like the one created by TOADPREP.SQL) and then run the TOADSECURITY.SQL script (found in the TEMPS subdirectory of the TOAD install directory and shown in Listing 1.2).
Listing 1.2 TOADSECURITY.SQL Script
/*
This is the script for setting up TOAD Features Security.
This file should be run after the TOAD user has been created
through toadprep.sql. Load this script into the SQL Editor
and press "Run as script." You will be prompted for TOAD's
password on your database. Then you will be prompted for the name
of the user (should be a DBA) who will serve as the TOAD
Security administrator. Bear in mind that users with the
DBA role are not bound by TOAD Security.
*/
/*
Date Description
---------- -------------------------------------------
03/05/2002 Recreated script for 7.3's rewrite of TOAD Security
*/
CONNECT TOAD
CREATE TABLE TOAD_RESTRICTIONS (
USER_NAME VARCHAR2(32) NOT NULL,
FEATURE VARCHAR2(20) NOT NULL,
CONSTRAINT TOAD_RES_PK
PRIMARY KEY ( FEATURE, USER_NAME ) );
REM grant all to the toad tables WITH grant option to any users
REM who will be using the TOAD Features Security Window to administer
REM TOAD security features.
REM
GRANT ALL ON TOAD_RESTRICTIONS TO &SOME_DBA_USER WITH GRANT OPTION;
Examine Figure 1.3 in more detail. The left side shows a list of all the available functions, also considered enabled. These fall into two categories: menu and non-menu. Menu refers to actual menu items within TOAD, whereas non-menu means functions that might be accessible from multiple places within TOAD. The right side then shows just those functions you want to remove or disable for that user or role. the DEVELOPER_JR has had the following removed:
Menu: Data subset (cannot run data subset wizard)
Menu: Profiler analysis (cannot run TOAD profiler)
Non-Menu: Analyze table (cannot analyze tables)
Non-Menu: DBA module (cannot access DBA features)
Non-Menu: Drop table (cannot drop tables)
Non-Menu: Truncate table (cannot truncate tables)
Note that the left side offers a Non-Menu choice of "Read only override." This is the new and preferable way to activate TOAD in read-only mode (see the preceding section). This is by far the easiest and most reliable method for defining your read-only TOAD users.
Enabling TOAD's PL/SQL Profiler Support
TOAD has a very capable PL/SQL profiler for detailed analysis of code execution behavior. But many people seem to have problems initially getting this feature to work. At first, the menu item and toolbar icon may not appear as enabled. That's because there are some TOAD schema objects that must be created. People next encounter issues on the profiler screen, because unknown to them there are Oracle profiler packages that must be created by the DBA in the SYS account (and which Oracle does not create by default). But in reality the steps to set up TOAD's profiler are quite easy:
Connect as SYS.
Run Oracle's RDBMS/ADMIN/PROFLOAD.SQL.
Connect as TOAD.
Run TOAD's TEMPS/TOADPROFILER.SQL.
If you don't create and use a TOAD schema, it would still be possible to enable TOAD profiler support in one of two ways:
Run TOAD's TEMPS/TOADPROFILER.SQL as SYSTEM or other DBA account, because it creates public synonyms and grants object privileges to public.
Run TOAD's TEMPS/TOADPROFILER.SQL as each and every TOAD user (just remember to comment out the public grants and synonyms portions near the bottom of the script).
Saving and Restoring All Your TOAD Settings
If there's one thing TOAD has, it's lots and lots of options. So as you use TOAD over time, you will naturally set many of these options so as to customize TOAD to the way you like to work. But what happens when you go to work on a different PC (for example, if you work on your neighbor's PC while yours is being serviced)? You lose all your settings because they are stored on the local PC hard drive in both the TOAD install directory and the TEMPS subdirectory. It would take some time if you lost those settings and had to visit every screen where they are defined throughout the tool and reset them. So what's needed is a TOAD settings backup and restoration utility, which is provided in the following examples.
The following Windows command processor batch script will back up all your most important TOAD settings. You simply modify the script's first four SET statements for your particular environment and save the script to a file called TOAD_SAVE.BAT. Then you can merely type TOAD_SAVE in a command window, or better yet, place an icon on your desktop so that you can save all your settings quickly and easily with a single click.
@echo off
set save_dir1=c:\temp\TOAD
set save_dir2=%save_dir1%\temps
set toad_dir1=c:\program files\quest software\toad
set toad_dir2=%toad_dir1%\temps
echo.
echo *** Saving TOAD Settings ***
echo.
echo Target:
echo save_dir1 = %save_dir1%
echo save_dir2 = %save_dir2%
echo.
echo Source:
echo toad_dir1 = %toad_dir1%
echo toad_dir2 = %toad_dir2%
if not exist "%save_dir1%" mkdir "%save_dir1%"
if not exist "%save_dir2%" mkdir "%save_dir2%"
del /Q %save_dir%\*.*
del /Q %save_dir%\*.*
echo.
echo Copying Files:
for %%i in ("%toad_dir1%"\*.ini,
"%toad_dir1%"\*.dat,
"%toad_dir1%"\*.sdf,
"%toad_dir1%"\*.jdf,
"%toad_dir1%"\*.flt,
"%toad_dir1%"\*.tni,
"%toad_dir1%"\*.tbl,
"%toad_dir1%"\*.tmd,
"%toad_dir1%"\*.lst) do copy /Y "%%i" "%save_dir1%\"
for %%i in ("%toad_dir2%"\*.ini,
"%toad_dir2%"\*.dat,
"%toad_dir2%"\*.sdf,
"%toad_dir2%"\*.jdf,
"%toad_dir2%"\*.flt,
"%toad_dir2%"\*.tni,
"%toad_dir2%"\*.tbl,
"%toad_dir2%"\*.tmd,
"%toad_dir2%"\*.lst) do copy /Y "%%i" "%save_dir2%\"
Similarly, the following Windows command processor batch script will restore all of your most important TOAD settings. You modify the script's first four SET statements for your particular environment and save the script to a file called TOAD_RESTORE.BAT. Then you can just type TOAD_RESTORE in a command window, or better yet, place an icon on your desktop so that you can restore all your settings with a single click.
@echo off
set save_dir1=c:\temp\TOAD
set save_dir2=%save_dir1%\temps
set toad_dir1=c:\program files\quest software\toad
set toad_dir2=%toad_dir1%\temps
echo.
echo *** Restoring TOAD Settings ***
echo.
echo Source:
echo save_dir1 = %save_dir1%
echo save_dir2 = %save_dir2%
echo.
echo Target:
echo toad_dir1 = %toad_dir1%
echo toad_dir2 = %toad_dir2%
echo.
echo Copying Files:
for %%i in ("%save_dir1%"\*.ini,
"%save_dir1%"\*.dat,
"%save_dir1%"\*.sdf,
"%save_dir1%"\*.jdf,
"%save_dir1%"\*.flt,
"%save_dir1%"\*.tni,
"%save_dir1%"\*.tbl,
"%save_dir1%"\*.tmd,
"%save_dir1%"\*.lst) do copy /Y "%%i" "%toad_dir1%\"
for %%i in ("%save_dir2%"\*.ini,
"%save_dir2%"\*.dat,
"%save_dir2%"\*.sdf,
"%save_dir2%"\*.jdf,
"%save_dir2%"\*.flt,
"%save_dir2%"\*.tni,
"%save_dir2%"\*.tbl,
"%save_dir2%"\*.tmd,
"%save_dir2%"\*.lst) do copy /Y "%%i" "%toad_dir2%\"
TOAD must be able to communicate with your Oracle database. That means TOAD must be able to both connect and then work with your database whether it's local (on PC) or remote (on server). This requires that the Oracle network client libraries exist on your PC for TOAD to call. Of course, Oracle in its infinite wisdom has renamed the client software and libraries as shown in Table 1.1.
Table 1.1. Oracle Connectivity Version
Terminology
Client Library
Oracle 7
SQL*Net
ORA7x.DLL
Oracle 8
Net8
ORACLIENT8.DLL
Oracle 8i
Net8
ORACLIENT8.DLL
Oracle 9i
Oracle Net
ORACLIENT9.DLL
Oracle 9iR2
Oracle Net
ORACLIENT9.DLL
Furthermore, you must also verify that your PC's environment variable for PATH includes the directory where those network client libraries are installed (that is, the Oracle installation directory). Of course, this too has changed over the Oracle versions and can also be modified by the person doing the install. Here are some examples of locations where they might be found:
C:\ORAWIN\BIN
C:\ORAWIN95\BIN
C:\ORANT\BIN
C:\ORACLE\BIN
C:\ORACLE\ORA92\BIN
Failure to install and reference these client libraries will prevent TOAD from functioning. However, you must also keep in mind your version of the client libraries versus the database you are talking to. Having client libraries from an older version of Oracle but talking to newer versions of Oracle is a recipe for disaster. You may encounter Oracle OCI API error messages from within TOAD. This might occur as TOAD attempts to call an OCI function for some new features or capability of the newer database version that the older network client library does not support.
For example, using an 8i client with a 9i database and attempting to work with columns whose data type is XMLTYPE will yield the cryptic message: OCI-21500: internal error code, arguments: [kocgpn129], [2], [], [], [], [], [], []. This is not a TOAD bug or lack of TOAD support for new features, but a simple user error of trying to use old or incompatible network client libraries with newer versions of the database. The best advice is to always use the latest and greatest network client, as it is always backward-compatible with prior database versions.
Understanding TOAD's Oracle-Based Security
Probably the most frequently asked question by DBAs new to TOAD is: "Will TOAD permit my developers to do things that they should not?" The simple answer is definitely not, because TOAD cannot override or supersede Oracle's security. TOAD users have only whatever roles, system privileges, or object grants that exist for them within the database. Thus they can do no more in TOAD than they could in SQL*Plus (they just can do it more easily and faster). To reiterate, TOAD only permits users to do whatever the DBA has granted them—there are no loopholes or exceptions.
But this does require the DBAs managing the Oracle schemas (that is, users) to have a very firm grasp on all the privileges they are actually handing out. For example, far too many DBAs still grant the predefined roles CONNECT, RESOURCE, and DBA to their users—even though Oracle states that these roles are there merely for backward compatibility and that you should create and grant your own customized roles. Unfortunately, many people seem to have missed this fact and still overuse the predefined roles. But some DBAs do not fully realize which system privileges the predefined roles grant. So granting a schema CONNECT means that the user can create clusters, database links, sequences, synonyms, tables, and views via TOAD, because those are the privileges that CONNECT possesses. So know your predefined roles well if you're going to use them. However, the recommendation is to create your own custom roles and grant those to your DBAs and developers,
-- Role: Junior Developer
-- Trusted to do some things
CREATE ROLE DEVELOPER_JR NOT IDENTIFIED;
--
-- Obviously required priv's
GRANT CREATE SESSION TO DEVELOPER_JR;
GRANT ALTER SESSION TO DEVELOPER_JR;
GRANT ALTER USER TO DEVELOPER_JR;
--
-- Junior Developer Priv's
GRANT CREATE PROCEDURE TO DEVELOPER_JR;
GRANT CREATE SEQUENCE TO DEVELOPER_JR;
GRANT CREATE SYNONYM TO DEVELOPER_JR;
GRANT CREATE TRIGGER TO DEVELOPER_JR;
GRANT CREATE TYPE TO DEVELOPER_JR;
GRANT CREATE VIEW TO DEVELOPER_JR;
-- Role: Senior Developer
-- Trusted to do most things
CREATE ROLE DEVELOPER_SR NOT IDENTIFIED;
--
-- Inherit All Junior Priv's
GRANT DEVELOPER_JR TO DEVELOPER_SR;
--
-- Senior Developer Priv's
GRANT CREATE DATABASE LINK TO DEVELOPER_SR;
GRANT CREATE DIMENSION TO DEVELOPER_SR;
GRANT CREATE INDEXTYPE TO DEVELOPER_JR;
GRANT CREATE LIBRARY TO DEVELOPER_SR;
GRANT CREATE MATERIALIZED VIEW TO DEVELOPER_SR;
GRANT CREATE OPERATOR TO DEVELOPER_JR;
GRANT CREATE TABLE TO DEVELOPER_SR;
Another area of possible security oversight is to forget the PUBLIC schema and its granted roles, system privileges, or object grants. For example, granting SELECT ANY TABLE to PUBLIC (which is generally not advisable) means that TOAD users can see the entire database's table data. So manage PUBLIC wisely. But do note that TOAD uses the data dictionary views for ALL_xxx and DBA_xxx that have been granted to PUBLIC. And don't worry about the DBA_xxx views; again, TOAD only allows users to see those based upon Oracle security. Thus a user must have SELECT ANY TABLE, SELECT ANY DICTIONARY, or SELECT_CATALOG_ROLE (depending on Oracle version and O7_DICTIONARY_ACCESSIBILITY setting) in order for TOAD to reference the DBA_xxx views. Remember that as you create roles for users who will be using TOAD's DBA module. For those users, one of the following security scenarios should be adopted:
O7_DICTIONARY_ACCESSIBILITY = true
O7_DICTIONARY_ACCESSIBILITY = true, SELECT ANY DICTIONARY granted, SELECT ANY TABLE not granted (second best recommendation)
O7_DICTIONARY_ACCESSIBILITY = true, SELECT ANY DICTIONARY granted, SELECT ANY TABLE granted
O7_DICTIONARY_ACCESSIBILITY = true, SELECT ANY DICTIONARY not granted, SELECT ANY TABLE not granted, SELECT_CATALOG_ROLE granted (best recommendation)
Creating the All-Important TOAD Schema
Strictly speaking, you do not have to create any database server-side objects in order to use TOAD. You can simply install TOAD on your PC and go on your merry way. But there are screens where a developer will require access to an Oracle explain plan table. For example, the SQL Editor window has a tab for displaying the explain plan for the current SQL statement. Thus TOAD will need access to a plan table in order to process and then display the resulting explain plan. You have three options here.
First, sometimes DBAs prefer to create a DBA schema-owned, general-purpose and shared explain plan table using Oracle's scripts. So the steps to implement might look something like this:
Connect as SYSTEM (or other DBA account).
Run Oracle's RDBMS/ADMIN/UTLXPLAN.SQL.
GRANT ALL ON SYSTEM.PLAN_TABLE TO PUBLIC
CREATE PUBLIC SYNONYM PLAN_TABLE FOR SYSTEM.PLAN_TABLE
TOAD can work with such a setup. You should merely set the TOAD options for the Explain Plan Table Name field under the Oracle category to PLAN_TABLE (meaning the public synonym for the general-purpose table set up by the DBA).
There are two caveats with this method. First, you must make sure the DBA schema-owned explain plan table is current for the Oracle version being used. It's really quite easy to forget to update this table with major Oracle upgrades. This can cause TOAD to encounter problems with missing columns (that is, a call to Oracle will try to populate newer columns that don't exist in the old plan table structure). And second, TOAD will not be able to save and recall plans if you use this method because TOAD requires its own plan table to support such operations. You must instead use one of the two remaining methods.
Second (and recommended), you can also create a special TOAD schema to own a general-purpose and shared explain plan table using TOAD's TOADPREP.SQL script (found in the TEMPS subdirectory of the TOAD install directory). TOADPREP.SQL first creates the TOAD schema, and then creates its required explain plan objects. To accomplish this implementation, the steps are as follows:
Edit TOAD's TEMPS/TOADPREP.SQL.
Connect as SYSTEM (or other DBA account).
Run TOAD's TEMPS/TOADPREP.SQL.
TOAD will now be able to support the save and recall of explain plans, as long as you remember to check Save Previous Explain Plan Results in the TOAD Options screen under the Oracle category, as shown in Figure 1.2.
The TOADPREP.SQL script has changed significantly with version 7.4. Although the script has always created both the TOAD schema and all its explain-plan required objects, it has been updated such that you only need to modify the first three DEFINE statements in the script in order to control the script's behavior. It used to be that you had to review the entire SQL script for possible changes. So the new TOADPREP.SQL script shown in Listing 1.1 is much easer to work with.
Listing 1.1 TOADPREP.SQL Script
REM This script will create TOAD objects in their own
REM schema. If you DO NOT want to create a unique system
REM schema for TOAD objects, load the file NOTOAD.SQL
REM
REM Otherwise, start a new Oracle connection as SYSTEM ( or
REM any other user with privileges to create a new USER)
REM and, while connected as that user, execute the following
REM by clicking the third toolbar button in a SQL Edit OR
REM by selecting the menu option "SQL_Window/Execute as Script"
REM
REM Ver Date Description
REM === ========== =======================================
REM 1.1 10/06/1999 1. Added STORAGE clauses to the table
REM create commands so that not too much
REM disk space will be allocated.
REM 2. Removed obsolete TOAD_TEMP and
REM TOAD_DEP_TEMP.
REM 1.2 11/17/1999 1. Changed index on TOAD_PLAN_TABLE from
REM unique to non-unique.
REM 1.3 05/23/2001 1. Added partition-related columns and
REM DISTRIBUTION to TOAD_PLAN_TABLE
REM 1.4 01/18/2001 1. Added OBJECTNAME function
REM 1.5 03/29/2002 Added EXECUTE ANY PROCEDURE
REM 1.6 03/31/2002 Reworked entire script so user custimizations
REM can be made via DEFINE variables in one place
REM --------- Make all changes right here --------------------
REM --------- Do not change the name of the TOAD user --------
DEFINE UPW_TOAD=TOAD
DEFINE DEF_TSPACE=USER_DATA
DEFINE TMP_TSPACE=TEMPORARY
REM ------------------ Create the TOAD User ------------------
DROP USER TOAD CASCADE;
CREATE USER TOAD
IDENTIFIED BY &UPW_TOAD
DEFAULT TABLESPACE &DEF_TSPACE
TEMPORARY TABLESPACE &TMP_TSPACE
QUOTA UNLIMITED ON &DEF_TSPACE
QUOTA 0K ON SYSTEM;
GRANT CONNECT TO TOAD;
GRANT RESOURCE TO TOAD;
GRANT CREATE PUBLIC SYNONYM TO TOAD;
--The following grant is only necessary if you intend to install the TOAD
--Profiler objects into the TOAD schema
GRANT EXECUTE ANY PROCEDURE TO TOAD;
REM ----------------- Drop public synonyms --------------------
DROP PUBLIC SYNONYM TOAD_PLAN_SQL;
DROP PUBLIC SYNONYM TOAD_PLAN_TABLE;
DROP PUBLIC SYNONYM TOAD_SEQ;
REM ----------------- Connect as TOAD -------------------------
CONNECT TOAD/&UPW_TOAD;
REM ----------------- Create the Explain Plan objects
DROP TABLE TOAD.TOAD_PLAN_SQL;
DROP TABLE TOAD.TOAD_PLAN_TABLE;
DROP SEQUENCE TOAD.TOAD_SEQ;
CREATE TABLE TOAD.TOAD_PLAN_SQL (
USERNAME VARCHAR2(30),
STATEMENT_ID VARCHAR2(32),
TIMESTAMP DATE,
STATEMENT VARCHAR2(2000) )
STORAGE (INITIAL 40K NEXT 24K);
CREATE UNIQUE INDEX TOAD.TPSQL_IDX ON
TOAD.TOAD_PLAN_SQL ( STATEMENT_ID );
CREATE TABLE TOAD.TOAD_PLAN_TABLE (
STATEMENT_ID VARCHAR2(32),
TIMESTAMP DATE,
REMARKS VARCHAR2(80),
OPERATION VARCHAR2(30),
OPTIONS VARCHAR2(30),
OBJECT_NODE VARCHAR2(128),
OBJECT_OWNER VARCHAR2(30),
OBJECT_NAME VARCHAR2(30),
OBJECT_INSTANCE NUMBER,
OBJECT_TYPE VARCHAR2(30),
SEARCH_COLUMNS NUMBER,
ID NUMBER,
COST NUMBER,
PARENT_ID NUMBER,
POSITION NUMBER,
CARDINALITY NUMBER,
OPTIMIZER VARCHAR2(255),
BYTES NUMBER,
OTHER_TAG VARCHAR2(255),
PARTITION_ID NUMBER,
PARTITION_START VARCHAR2(255),
PARTITION_STOP VARCHAR2(255),
DISTRIBUTION VARCHAR2(30),
OTHER LONG)
STORAGE(INITIAL 80K NEXT 36K) ;
CREATE INDEX TOAD.TPTBL_IDX ON
TOAD.TOAD_PLAN_TABLE ( STATEMENT_ID );
CREATE SEQUENCE TOAD.TOAD_SEQ START WITH 1 CACHE 20;
CREATE PUBLIC SYNONYM TOAD_PLAN_SQL FOR TOAD.TOAD_PLAN_SQL;
CREATE PUBLIC SYNONYM TOAD_PLAN_TABLE FOR TOAD.TOAD_PLAN_TABLE;
CREATE PUBLIC SYNONYM TOAD_SEQ FOR TOAD.TOAD_SEQ;
GRANT SELECT, INSERT, UPDATE, DELETE ON TOAD.TOAD_PLAN_SQL TO PUBLIC;
GRANT SELECT, INSERT, UPDATE, DELETE ON TOAD.TOAD_PLAN_TABLE TO PUBLIC;
GRANT SELECT, ALTER ON TOAD.TOAD_SEQ TO PUBLIC;
REM -------- Create the ObjectName function for use in Kill/Trace -----------
DROP FUNCTION TOAD.MYOBJECTNAME;
CREATE OR REPLACE function TOAD.ObjectName(in_object_id in number) return
varchar
is
return_string varchar2(100);
begin
select OWNER||'.'||OBJECT_NAME
into return_string
from all_objects
where object_id = in_object_id;
return return_string;
end ObjectName;
/
GRANT EXECUTE ON TOAD.OBJECTNAME TO PUBLIC;
Third and finally, you can also create a private TOAD explain plan table and all its required objects per TOAD user via the NOTOAD.SQL script (also found in the TEMPS subdirectory of the TOAD install directory). To accomplish this implementation, the steps are as follows:
Connect as each and every TOAD user.
Run TOAD's TEMPS/NOTOAD.SQL.
This method is supported for backward compatibility, but it's very hard to imagine a case where you would want to choose this implementation option. This method would require every TOAD user to have CREATE TABLE privilege, CREATE SEQUENCE privilege, and some tablespace quota on at least one tablespace. Plus it would result in numerous additional database objects as a whole (that is, two tables, two indexes, and one sequence created per TOAD user). But the option does exist for those who want to use it. The only advantage to this approach is that, like the prior method, it too supports the save and recall of explain plans. Because this method is not recommended, the NOTOAD.SQL script is not shown.
Activating TOAD's "Read-Only" Mode
Most people don't realize that TOAD comes with two modes of operation entirely under their control. TOAD can operate in either read/write or read-only mode, simply controlled by the license files in the TOAD install directory. When the TOAD.LIC file is a copy of the FULLTOAD.LIC file (default), TOAD operates in read/write mode. And when the TOAD.LIC file is a copy of the READONLY.LIC file, TOAD operates in read-only mode. Note that read-only refers to the fact that users cannot save or commit anything to the database. However, read-only users are still permitted to create, modify, and save other files on the local PC. They cannot do anything but read database data.
Note that this was the original method by which TOAD supported a read-only mode of operation. Beginning in version 7.3, TOAD's entirely new advanced security offering (see the following section) offers a much cleaner and simpler method for making TOAD read-only.
Using TOAD's New Advanced Security
Beginning with version 7.3, TOAD offers an entirely new and completely customizable security mechanism. With it, you can very easily define what features or functions should work in TOAD by either database user or role. For example, you can take the DEVELOPER_JR database role from the prior section and further control what a junior developer can do within TOAD as shown in Figure 1.3. This screen is callable from the main menu at Tools, Toad Security. However, to use this screen, you must first have a TOAD schema (like the one created by TOADPREP.SQL) and then run the TOADSECURITY.SQL script (found in the TEMPS subdirectory of the TOAD install directory and shown in Listing 1.2).
Listing 1.2 TOADSECURITY.SQL Script
/*
This is the script for setting up TOAD Features Security.
This file should be run after the TOAD user has been created
through toadprep.sql. Load this script into the SQL Editor
and press "Run as script." You will be prompted for TOAD's
password on your database. Then you will be prompted for the name
of the user (should be a DBA) who will serve as the TOAD
Security administrator. Bear in mind that users with the
DBA role are not bound by TOAD Security.
*/
/*
Date Description
---------- -------------------------------------------
03/05/2002 Recreated script for 7.3's rewrite of TOAD Security
*/
CONNECT TOAD
CREATE TABLE TOAD_RESTRICTIONS (
USER_NAME VARCHAR2(32) NOT NULL,
FEATURE VARCHAR2(20) NOT NULL,
CONSTRAINT TOAD_RES_PK
PRIMARY KEY ( FEATURE, USER_NAME ) );
REM grant all to the toad tables WITH grant option to any users
REM who will be using the TOAD Features Security Window to administer
REM TOAD security features.
REM
GRANT ALL ON TOAD_RESTRICTIONS TO &SOME_DBA_USER WITH GRANT OPTION;
Examine Figure 1.3 in more detail. The left side shows a list of all the available functions, also considered enabled. These fall into two categories: menu and non-menu. Menu refers to actual menu items within TOAD, whereas non-menu means functions that might be accessible from multiple places within TOAD. The right side then shows just those functions you want to remove or disable for that user or role. the DEVELOPER_JR has had the following removed:
Menu: Data subset (cannot run data subset wizard)
Menu: Profiler analysis (cannot run TOAD profiler)
Non-Menu: Analyze table (cannot analyze tables)
Non-Menu: DBA module (cannot access DBA features)
Non-Menu: Drop table (cannot drop tables)
Non-Menu: Truncate table (cannot truncate tables)
Note that the left side offers a Non-Menu choice of "Read only override." This is the new and preferable way to activate TOAD in read-only mode (see the preceding section). This is by far the easiest and most reliable method for defining your read-only TOAD users.
Enabling TOAD's PL/SQL Profiler Support
TOAD has a very capable PL/SQL profiler for detailed analysis of code execution behavior. But many people seem to have problems initially getting this feature to work. At first, the menu item and toolbar icon may not appear as enabled. That's because there are some TOAD schema objects that must be created. People next encounter issues on the profiler screen, because unknown to them there are Oracle profiler packages that must be created by the DBA in the SYS account (and which Oracle does not create by default). But in reality the steps to set up TOAD's profiler are quite easy:
Connect as SYS.
Run Oracle's RDBMS/ADMIN/PROFLOAD.SQL.
Connect as TOAD.
Run TOAD's TEMPS/TOADPROFILER.SQL.
If you don't create and use a TOAD schema, it would still be possible to enable TOAD profiler support in one of two ways:
Run TOAD's TEMPS/TOADPROFILER.SQL as SYSTEM or other DBA account, because it creates public synonyms and grants object privileges to public.
Run TOAD's TEMPS/TOADPROFILER.SQL as each and every TOAD user (just remember to comment out the public grants and synonyms portions near the bottom of the script).
Saving and Restoring All Your TOAD Settings
If there's one thing TOAD has, it's lots and lots of options. So as you use TOAD over time, you will naturally set many of these options so as to customize TOAD to the way you like to work. But what happens when you go to work on a different PC (for example, if you work on your neighbor's PC while yours is being serviced)? You lose all your settings because they are stored on the local PC hard drive in both the TOAD install directory and the TEMPS subdirectory. It would take some time if you lost those settings and had to visit every screen where they are defined throughout the tool and reset them. So what's needed is a TOAD settings backup and restoration utility, which is provided in the following examples.
The following Windows command processor batch script will back up all your most important TOAD settings. You simply modify the script's first four SET statements for your particular environment and save the script to a file called TOAD_SAVE.BAT. Then you can merely type TOAD_SAVE in a command window, or better yet, place an icon on your desktop so that you can save all your settings quickly and easily with a single click.
@echo off
set save_dir1=c:\temp\TOAD
set save_dir2=%save_dir1%\temps
set toad_dir1=c:\program files\quest software\toad
set toad_dir2=%toad_dir1%\temps
echo.
echo *** Saving TOAD Settings ***
echo.
echo Target:
echo save_dir1 = %save_dir1%
echo save_dir2 = %save_dir2%
echo.
echo Source:
echo toad_dir1 = %toad_dir1%
echo toad_dir2 = %toad_dir2%
if not exist "%save_dir1%" mkdir "%save_dir1%"
if not exist "%save_dir2%" mkdir "%save_dir2%"
del /Q %save_dir%\*.*
del /Q %save_dir%\*.*
echo.
echo Copying Files:
for %%i in ("%toad_dir1%"\*.ini,
"%toad_dir1%"\*.dat,
"%toad_dir1%"\*.sdf,
"%toad_dir1%"\*.jdf,
"%toad_dir1%"\*.flt,
"%toad_dir1%"\*.tni,
"%toad_dir1%"\*.tbl,
"%toad_dir1%"\*.tmd,
"%toad_dir1%"\*.lst) do copy /Y "%%i" "%save_dir1%\"
for %%i in ("%toad_dir2%"\*.ini,
"%toad_dir2%"\*.dat,
"%toad_dir2%"\*.sdf,
"%toad_dir2%"\*.jdf,
"%toad_dir2%"\*.flt,
"%toad_dir2%"\*.tni,
"%toad_dir2%"\*.tbl,
"%toad_dir2%"\*.tmd,
"%toad_dir2%"\*.lst) do copy /Y "%%i" "%save_dir2%\"
Similarly, the following Windows command processor batch script will restore all of your most important TOAD settings. You modify the script's first four SET statements for your particular environment and save the script to a file called TOAD_RESTORE.BAT. Then you can just type TOAD_RESTORE in a command window, or better yet, place an icon on your desktop so that you can restore all your settings with a single click.
@echo off
set save_dir1=c:\temp\TOAD
set save_dir2=%save_dir1%\temps
set toad_dir1=c:\program files\quest software\toad
set toad_dir2=%toad_dir1%\temps
echo.
echo *** Restoring TOAD Settings ***
echo.
echo Source:
echo save_dir1 = %save_dir1%
echo save_dir2 = %save_dir2%
echo.
echo Target:
echo toad_dir1 = %toad_dir1%
echo toad_dir2 = %toad_dir2%
echo.
echo Copying Files:
for %%i in ("%save_dir1%"\*.ini,
"%save_dir1%"\*.dat,
"%save_dir1%"\*.sdf,
"%save_dir1%"\*.jdf,
"%save_dir1%"\*.flt,
"%save_dir1%"\*.tni,
"%save_dir1%"\*.tbl,
"%save_dir1%"\*.tmd,
"%save_dir1%"\*.lst) do copy /Y "%%i" "%toad_dir1%\"
for %%i in ("%save_dir2%"\*.ini,
"%save_dir2%"\*.dat,
"%save_dir2%"\*.sdf,
"%save_dir2%"\*.jdf,
"%save_dir2%"\*.flt,
"%save_dir2%"\*.tni,
"%save_dir2%"\*.tbl,
"%save_dir2%"\*.tmd,
"%save_dir2%"\*.lst) do copy /Y "%%i" "%toad_dir2%\"
Subscribe to:
Posts (Atom)