How to fix - 'PLAN_TABLE' is old version

DBAs and developers usually use GUI tools to review the explain plan of an Oracle SQL - Toad, SQL Developer, Golden/Benthic, OEM Grid Control. Recently I adopted SQLPLUS methodology. In one of my instances, I received the following warning/note when I performed explain plan with 'dbms_xplan.display()':

Note
-----
   - 'PLAN_TABLE' is old version

What we miss here with explain plan is the predicate section. The predicate section is vital to understand the joins and filter conditions in SQL Tuning.

Before jumping to fixing this warning, I want to share the basic information of how plan table is created, how to generate explain plan and what has changed.PLAN_TABLE can be created in Oracle Database using the script $ORACLE_HOME/rdbms/admin/utlxplan.sql. This will create a local table in the schema you have connected to run the script. We have to create PLAN_TABLE in every schema we need to use it.

To generate explain plan:

EXPLAIN PLAN FOR
  SELECT emp_no, last_name, salary FROM employees;

To view the explain plan:

SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY());

What has changed:

Oracle got rid of the need for users to create a PLAN_TABLE in every schema by creating a global temporary table in the SYS  schema, creating a public synonym and granting all the required privileges to PUBLIC. Please do note that this means the PLAN_TABLE is shared across different schemas but every session gets its own copy of the PLAN_TABLE in the temporary tablespace. To create the new PLAN_TABLE, you need to use $ORACLE_HOME/rdbms/admin/catplan.sql.

Fix for -'PLAN_TABLE' is old version :

sqlplus / as sysdba
SQL> select  owner, object_type, object_name from all_objects where object_name like 'PLAN_TABLE%';
SYS                            TABLE               PLAN_TABLE
SYS                            TABLE               PLAN_TABLE$
PUBLIC                         SYNONYM             PLAN_TABLE
SYSTEM                         TABLE               PLAN_TABLE
APPS                           TABLE               PLAN_TABLE

SQL> drop table plan_table;
SQL>drop table system.plan_table;
SQL>drop table apps.plan_table;
SQL>drop table plan_table$;
SQL>drop public synonym plan_table;
SQL>@$ORACLE_HOME/rdbms/admin/catplan.sql

SQL>select  owner, object_type, object_name from all_objects where object_name like 'PLAN_TABLE%';
SYS                            TABLE               PLAN_TABLE$
PUBLIC                         SYNONYM             PLAN_TABLE
SQL>@dbmsxpln.sql
SQL>@prvtxpln.plb
SQL>@utlrp.sql

dbmsxpln.sql reload dbms_xplan specification,
prvtxpln.plb reloads dbms_xplan,
utlrp.sql is to compile the invalids.

Reference:

http://jonathanlewis.wordpress.com/2010/01/25/old-plan_table/