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). 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:

1.
Edit TOAD’s TEMPS/TOADPREP.SQL.

2.
Connect as SYSTEM (or other DBA account).

3.
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:

1.
Connect as each and every TOAD user.

2.
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.

..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset
18.219.156.22