This content has been marked as final. Show 8 replies
saswat . wrote:
[Error] Execution (11: 12): ORA-01652: unable to extend temp segment by 64 in tablespace TEMP.
enough free space is available in TEMP tablespace
01652, 00000, "unable to extend temp segment by %s in tablespace %s" // *Cause: Failed to allocate an extent of the required number of blocks for // a temporary segment in the tablespace indicated. // *Action: Use ALTER TABLESPACE ADD DATAFILE statement to add one or more // files to the tablespace indicated.
saswat . wrote:error message indicates otherwise.
Sufficient free space is available in TEMP TS
after error is thrown any consumed space gets released.
Either you are mistaken or Oracle is mistaken.
If Oracle is correct, then you need to change something to avoid this error.
If you are correct & Oracle is wrongly throwing error, then you need to file a Bug Report with Oracle.
You decide what you do next.
Oracle releases space in TEMP tablespaces when session ends but it does not shrink datafile back. So it could be possible there your oracle session is taking more than temp's available space and when you get the error(Session ends) , it releases the space back. That is why you are seeing free space in temp.
What are you trying to do when you get this error?
Text: unable to extend temp segment by %s in tablespace %s
Cause: Failed to allocate an extent for temp segment in tablespace.
Action: Use ALTER TABLESPACE ADD DATAFILE statement to add one or more
files to the tablespace indicated or create the object in another
*** Important: The notes below are for experienced users - See Note:22080.1
+[mod. action: removed the metalink note content]+
Edited by: N Gasparotto on Mar 22, 2013 9:01 AM
TEMP space usage is dynamic and ... well ... temporary.
Problem temp usage is generally related to poor execution plan estimates leading to poor workarea sizing for hash join operations and sorting, which then spill to disk.
Identify the SQL responsible and tune it.
Look at V$SQL_WORKAREA / V$SQL_WORKAREA_ACTIVE and V$TEMPSEG_USAGE whilst the usage is ongoing,
Also in later versions of Oracle ASH via V$ACTIVE_SESSION_HISTORY/DBA_HIST_ACTIVE_SESS_HISTORY.TEMP_SPACE_ALLOCATED (ASH requires Diagnostic pack license).