You don’t need the PLAN_TABLE table
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).
