Home » SQL & PL/SQL » Client Tools » Toad explan plan script
Toad explan plan script [message #15641] Mon, 18 October 2004 04:40 Go to next message
Solomon
Messages: 5
Registered: August 2002
Junior Member
Can anybody send me the Toad explain plan (toadprep.sql) script to me. My toad explain plan is not working.

Thanks in advance..
Solomon
Re: Toad explan plan script [message #15650 is a reply to message #15641] Mon, 18 October 2004 09:19 Go to previous messageGo to next message
Todd Barry
Messages: 4819
Registered: August 2001
Senior Member
REM This script was created by version 8.0.0.31 of the TOAD Server Side Objects Wizard
SET ECHO OFF
SET LINESIZE 200
DEFINE OWNER = ''

REM This script should be run as the TOAD user.

Prompt ============================================================================
Prompt Creating Explain Plan objects in  schema
Prompt ============================================================================

DEFINE tablespace_info = 'TABLESPACE &&USERS'
DEFINE tablespace_ind_info = 'TABLESPACE &&INDX'

Prompt Creating TABLE TOAD_PLAN_SQL
CREATE TABLE &&owner..toad_plan_sql (
username     VARCHAR2(30),
statement_id VARCHAR2(32),
timestamp    DATE,
statement   VARCHAR2(2000) )
&&tablespace_info; 

Prompt Creating INDEX TPSQL_IDX
CREATE UNIQUE INDEX &owner..tpsql_idx ON
&&owner..toad_plan_sql ( STATEMENT_ID )
&&tablespace_ind_info;

Prompt Creating TABLE TOAD_PLAN_TABLE
CREATE TABLE &&owner..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)
&&tablespace_info ; 

Prompt Creating INDEX TPTBL_IDX
CREATE INDEX &&owner..tptbl_idx ON
&&owner..toad_plan_table ( STATEMENT_ID )
&&tablespace_ind_info;

Prompt ============================================================================
Prompt Adding public synonyms for Explain Plan objects
Prompt ============================================================================

Prompt Creating public synonym TOAD_PLAN_SQL
CREATE PUBLIC SYNONYM TOAD_PLAN_SQL FOR &&OWNER..TOAD_PLAN_SQL;

Prompt Creating public synonym TOAD_PLAN_TABLE
CREATE PUBLIC SYNONYM TOAD_PLAN_TABLE FOR &&OWNER..TOAD_PLAN_TABLE;

Prompt ============================================================================
Prompt Granting privileges to PUBLIC on Explain Plan tables
Prompt ============================================================================

Prompt Granting SELECT, INSERT, UPDATE, DELETE on TOAD_PLAN_SQL to PUBLIC
GRANT SELECT, INSERT, UPDATE, DELETE ON &&OWNER..TOAD_PLAN_SQL TO PUBLIC;

Prompt Granting SELECT, INSERT, UPDATE, DELETE on TOAD_PLAN_TABLE to PUBLIC
GRANT SELECT, INSERT, UPDATE, DELETE ON &&OWNER..TOAD_PLAN_TABLE TO PUBLIC;
Re: Toad explan plan script [message #15670 is a reply to message #15650] Mon, 18 October 2004 20:23 Go to previous message
Solomon
Messages: 5
Registered: August 2002
Junior Member
Thanks Todd..
Previous Topic: Expalin Plan and Auto Trace in Toad
Next Topic: Installation /use of sqlplus with oracle 10g
Goto Forum:
  


Current Time: Tue Aug 11 08:47:29 CDT 2020