2

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?

2

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.