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?
Best Answer
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;