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:
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). The TOAD Options screen is launched from the main menu at View, Options and is shown in Figure 1.2. Figure 1.2. TOAD Options: Oracle explain plans. 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 :
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 ScriptREM 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:
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. |
EAN: 2147483647
Pages: 171