You don’t need the PLAN_TABLE table

Franck Pachot
Jan 10, 2019 · 2 min read

This post is about the following error you may get when looking at an execution plan after setting the current_schema:

Error: cannot fetch last explain plan from PLAN_TABLE

It is related with old versions and relics from even older versions.

In the old times, PLAN_TABLE was a permanent shared regular table created by utlxplan.sql. Since Oracle 8i which introduced Global Temporary Tables, the PLAN_TABLE public synonym refers to SYS.PLAN_TABLE$ which is a GTT, not shared and emptied at the end of your session.

When I want to tune a query, I usually connect with my DBA user and change my session schema to the application one, so that I can explain or run the user query without having to prefix all tables. But when there is a PLAN_TABLE in the current schema, DBMS_XPLAN.DISPLAY may fail:

SQL> alter session set current_schema=SCOTT;Session altered.SQL> explain plan for select * from dual;Explained.SQL> select * from table(dbms_xplan.display);PLAN_TABLE_OUTPUT                                                  
--------------------------------------------------------------------
Error: cannot fetch last explain plan from PLAN_TABLE

I can check that in addition to the SYS GTT and PUBLIC synonyms, there’s a PLAN_TABLE in this schema

SQL> select owner,object_name,object_type,created from all_objects where object_name like 'PLAN_TABLE%' and owner in (sys_context('userenv','current_schema'),'PUBLIC','SYS');OWNER    OBJECT_NAME OBJECT_T CREATED                    
-------- ----------- -------- -----------------
SCOTT PLAN_TABLE TABLE 07-MAR-2014 09:15
PUBLIC PLAN_TABLE SYNONYM 29-JAN-2014 21:47
SYS PLAN_TABLE$ TABLE 29-JAN-2014 21:47

This is annoying and not needed, then I drop it

SQL> drop table PLAN_TABLE;Table PLAN_TABLE dropped.

Of course, you must be sure that this is not an application table with the same name. Just select to see what is there, and in case of doubt, rename it instead of drop:

SQL> rename PLAN_TABLE to "old plan table, delete me";Table renamed.

Now EXPLAIN PLAN and DBMS_XPLAN.DISPLAY work as expected:

SQL> select * from table(dbms_xplan.display);Plan hash value: 272002086

-------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Time |
-------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 2 | 00:00:01 |
| 1 | TABLE ACCESS FULL| DUAL | 1 | 2 | 00:00:01 |
-------------------------------------------------------------

Actually, I think the problem does not appear in recent versions. I checked on 18c and EXPLAIN PLAN inserts into the current schema (it was inserted in the connected user schema in 11g).

Welcome to a place where words matter. On Medium, smart voices and original ideas take center stage - with no ads in sight. Watch
Follow all the topics you care about, and we’ll deliver the best stories for you to your homepage and inbox. Explore
Get unlimited access to the best stories on Medium — and support writers while you’re at it. Just $5/month. Upgrade

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store