How to enable missing Tempspc column in Oracle explain plan

Question

I am trying to debug "ORA-01652: unable to extend temp segment" from a data warehouse ETL batch job, but my plan_table_output view from explain plan does not show the "Tempspc" column. But I do not seem to have the "'PLAN_TABLE' is old version" problem.

Version: Oracle Database 12c Enterprise Edition Release 12.1.0.1.0

Query: select * from table(dbms_xplan.display_awr(sql_id, null, null, 'ALL'));

Columns returned: Id, Operation, Name, Rows, Bytes, Cost (%CPU), Time

Notes returned: - dynamic statistics used: dynamic sampling (level=2)

The following query shows that all columns in the global temp plan_table match the @catplan.sql admin script:

select * from all_tab_columns where owner = 'SYS' and table_name = 'PLAN_TABLE$' order by column_name;

And this query shows the public synonyms looks ok:

select owner, object_type, object_name from all_objects where object_name like 'PLAN_TABLE%';

OWNER OBJECT_TYPE OBJECT_NAME 
------ ----------- ----------- 
SYS TABLE PLAN_TABLE$ 
PUBLIC SYNONYM PLAN_TABLE

Does anyone have advice on how to enable the missing Tempspc column in my explain plan?

Answer 1

This TempSpc column will only be shown if Your query will need any Temporary space like if you are sorting a big chunk of records and viewing it.

If query don't need any temporary space, this column will not be shown. This varies from explain plan of one query to another, so you will not find it in every explain plan result.

As per this Oracle Page;

Temporary space, in bytes, used by the operation as estimated by the query optimizer's approach. For statements that use the rule-based approach, or for operations that do not use any temporary space, this column is null.