Oracle EXPLAIN PLAN FOR Returns Nothing

Question

I run the following query on an Oracle database:

EXPLAIN PLAN FOR
SELECT *
FROM table_name

However, it's not returning any data. When I delete the EXPLAIN PLAN FOR clause, the query does run as expected. Thanks for the help!

In case it's relevant, I'm accessing the database through Teradata and also a Jupyter IPython notebook.

Answer 1

From Using EXPLAIN PLAN:

The PLAN_TABLE is automatically created as a global temporary table to hold the output of an EXPLAIN PLAN statement for all users. PLAN_TABLE is the default sample output table into which the EXPLAIN PLAN statement inserts rows describing execution plans

EXPLAIN PLAN FOR  SELECT last_name FROM employees;

This explains the plan into the PLAN_TABLE table. You can then select the execution plan from PLAN_TABLE.

Displaying PLAN_TABLE Output

• UTLXPLS.SQL
• UTLXPLP.SQL
• DBMS_XPLAN.DISPLAY table function

I suggest to use:

EXPLAIN PLAN FOR SELECT * FROM table_name;

SELECT * FROM TABLE(dbms_xplan.display);