maintaining a plan table in oracle

Question

If PLAN_TABLE is created automatically post Oracle 9i, then why do I get

"Table or view does not exists"

while trying to explain plan. I am using oracle 11.1. I am logged in with schema owner account.

Answer 1

why do I get "Table or view does not exists" while trying to explain plan.

There are two possibilities:

• The USER you have logged in as, doesn't have the privilege on PLAN_TABLE.

• PLAN_TABLE actually doesn't exist.

If you could get point 1 fixed, then well and good.

If plan table doesn't exist, then you could create your own PLAN_TABLE by executing the Oracle provided script utlxplan.sql(given that the user has the create table privilege):

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning and Automatic Storage Management options

SQL> @?/rdbms/admin/utlxplan.sql

Table created.

SQL> DESC PLAN_TABLE;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------

 STATEMENT_ID                                       VARCHAR2(30)
 PLAN_ID                                            NUMBER
 TIMESTAMP                                          DATE
 REMARKS                                            VARCHAR2(4000)
 OPERATION                                          VARCHAR2(30)
 OPTIONS                                            VARCHAR2(255)
 OBJECT_NODE                                        VARCHAR2(128)
 OBJECT_OWNER                                       VARCHAR2(30)
 OBJECT_NAME                                        VARCHAR2(30)
 OBJECT_ALIAS                                       VARCHAR2(65)
 OBJECT_INSTANCE                                    NUMBER(38)
 OBJECT_TYPE                                        VARCHAR2(30)
 OPTIMIZER                                          VARCHAR2(255)
 SEARCH_COLUMNS                                     NUMBER
 ID                                                 NUMBER(38)
 PARENT_ID                                          NUMBER(38)
 DEPTH                                              NUMBER(38)
 POSITION                                           NUMBER(38)
 COST                                               NUMBER(38)
 CARDINALITY                                        NUMBER(38)
 BYTES                                              NUMBER(38)
 OTHER_TAG                                          VARCHAR2(255)
 PARTITION_START                                    VARCHAR2(255)
 PARTITION_STOP                                     VARCHAR2(255)
 PARTITION_ID                                       NUMBER(38)
 OTHER                                              LONG
 OTHER_XML                                          CLOB
 DISTRIBUTION                                       VARCHAR2(30)
 CPU_COST                                           NUMBER(38)
 IO_COST                                            NUMBER(38)
 TEMP_SPACE                                         NUMBER(38)
 ACCESS_PREDICATES                                  VARCHAR2(4000)
 FILTER_PREDICATES                                  VARCHAR2(4000)
 PROJECTION                                         VARCHAR2(4000)
 TIME                                               NUMBER(38)
 QBLOCK_NAME                                        VARCHAR2(30)

If everything fails, talk to your DBA.