By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,823 Members | 730 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,823 IT Pros & Developers. It's quick & easy.

TOADS Explain plan

P: 19
Hi,

I want the explain plan. But when I clicked on the explain plan it says ORA-02404: specified plan table not found. I investigated on net and some books. I was asked to load the utlxplan.sql. I did that but of no use. I also changed the name of the plan table in TOAD, still no use. New thing I heard recently is to run TOADPREP.SQL. But this script is not available on our server($ORACLE_HOME/rdbms/admin). Can someone please suggest me what to do in order to get the explain plan.

Thanks,
Jun 12 '08 #1
Share this Question
Share on Google+
3 Replies


100+
P: 153
Can you query the plan table by itself? you should be able to run something like
select * from plan_table
and get no rows returned. if not you either havent created the table or dont have privileges to use it.

another thing to check is in the properties if toad has called the table TOAD_PLAN_TABLE rather than just plan_table.

the other thing you are calling (once you have explained a query) is this
select * from table(dbms_xplan.display);
Jun 20 '08 #2

QVeen72
Expert 100+
P: 1,445
Hi,

First Create a Plan_Table, use the above said utility or you can run this :

Expand|Select|Wrap|Line Numbers
  1. CREATE TABLE PLAN_TABLE (
  2.   STATEMENT_ID                    VARCHAR2(30),
  3.   TIMESTAMP                       DATE,
  4.   REMARKS                         VARCHAR2(80),
  5.   OPERATION                       VARCHAR2(30),
  6.   OPTIONS                         VARCHAR2(30),
  7.   OBJECT_NODE                     VARCHAR2(128),
  8.   OBJECT_OWNER                    VARCHAR2(30),
  9.   OBJECT_NAME                     VARCHAR2(30),
  10.   OBJECT_INSTANCE                 NUMBER(38),
  11.   OBJECT_TYPE                     VARCHAR2(30),
  12.   OPTIMIZER                       VARCHAR2(255),
  13.   SEARCH_COLUMNS                  NUMBER,
  14.   ID                              NUMBER(38),
  15.   PARENT_ID                       NUMBER(38),
  16.   POSITION                        NUMBER(38),
  17.   COST                            NUMBER(38),
  18.   CARDINALITY                     NUMBER(38),
  19.   BYTES                           NUMBER(38),
  20.   OTHER_TAG                       VARCHAR2(255),
  21.   PARTITION_START                 VARCHAR2(255),
  22.   PARTITION_STOP                  VARCHAR2(255),
  23.   PARTITION_ID                    NUMBER(38),
  24.   OTHER                           LONG,
  25.   DISTRIBUTION                    VARCHAR2(30)
  26. );
  27.  
Regards
Veena
Jun 22 '08 #3

P: 19
Thank you very much Veena it worked!!

regards,
Ravi

Hi,

First Create a Plan_Table, use the above said utility or you can run this :

Expand|Select|Wrap|Line Numbers
  1. CREATE TABLE PLAN_TABLE (
  2.   STATEMENT_ID                    VARCHAR2(30),
  3.   TIMESTAMP                       DATE,
  4.   REMARKS                         VARCHAR2(80),
  5.   OPERATION                       VARCHAR2(30),
  6.   OPTIONS                         VARCHAR2(30),
  7.   OBJECT_NODE                     VARCHAR2(128),
  8.   OBJECT_OWNER                    VARCHAR2(30),
  9.   OBJECT_NAME                     VARCHAR2(30),
  10.   OBJECT_INSTANCE                 NUMBER(38),
  11.   OBJECT_TYPE                     VARCHAR2(30),
  12.   OPTIMIZER                       VARCHAR2(255),
  13.   SEARCH_COLUMNS                  NUMBER,
  14.   ID                              NUMBER(38),
  15.   PARENT_ID                       NUMBER(38),
  16.   POSITION                        NUMBER(38),
  17.   COST                            NUMBER(38),
  18.   CARDINALITY                     NUMBER(38),
  19.   BYTES                           NUMBER(38),
  20.   OTHER_TAG                       VARCHAR2(255),
  21.   PARTITION_START                 VARCHAR2(255),
  22.   PARTITION_STOP                  VARCHAR2(255),
  23.   PARTITION_ID                    NUMBER(38),
  24.   OTHER                           LONG,
  25.   DISTRIBUTION                    VARCHAR2(30)
  26. );
  27.  
Regards
Veena
Jun 26 '08 #4

Post your reply

Sign in to post your reply or Sign up for a free account.