ORA-1652: unable to extend temp segment by 32 in tablespace TEMP [message #668104] |
Wed, 07 February 2018 02:40 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.gravatar.com/avatar/69cbc579a152d58eb551354b3e93fa13?s=64&d=mm&r=g) |
daulat01
Messages: 62 Registered: May 2011 Location: Delhi
|
Member |
|
|
Dear Experts,
I am getting an "ORA-1652: unable to extend temp segment by 32 in tablespace TEMP" very frequently. We have around 5 GB of free space in TEMP tablespace.
SQL> select TABLESPACE_NAME,round(BYTES_USED/(1024*1024*1024)) "USED BYTES-GB", ROUND(BYTES_FREE/(1024*1024)) "FREE BYTES" from V$TEMP_SPACE_HEADER;
TABLESPACE_NAME USED BYTES-GB FREE BYTES
------------------------------ ------------- ----------
TEMP 21 0
TEMP 21 0
TEMP 15 4977
TEMP 21 0
TEMP 20 0
SQL> Select tablespace_name, file_name, sum(bytes)/1024/1024/1024 GB from dba_temp_files group by tablespace_name, file_name; (Files associated with TEMP tablespace)
TABLESPACE_NAME FILE_NAME GB
-----------------------------------------------------------------
TEMP /mnt/tfr/oracle/oradata/tfrdb/temp04.dbf 20
TEMP /mnt/tfr/oracle/oradata/tfrdb/temp01.dbf 20
TEMP /mnt/tfr/oracle/oradata/tfrdb/temp05.dbf 21
TEMP /mnt/tfr/oracle/oradata/tfrdb/temp03.dbf 21
TEMP /mnt/tfr/oracle/oradata/tfrdb/temp02.dbf 21
I have searched queries which are consuming temp space:
SQL> select u.username, s.sql_fulltext, u.segtype, u.extents, u.blocks from v$tempseg_usage u, v$sql s where s.sql_id = u.sql_id;
USERNAME SQL_FULLTEXT SEGTYPE EXTENTS BLOCKS
------------------------------ -------------------------------------------------------------------------------- --------- ---------- ----------
TFR_REP SELECT LOADER as loader, LOADED_TIME as loadedTime, LOAD_STATUS as loadStatus, L LOB_DATA 1 32
TFR_REP UPDATE LOADER_REPORT_BATCH_RECORD set PHASE = :1 WHERE BATCH_ID = :2 LOB_DATA 1 32
TFR_REP UPDATE LOADER_REPORT_BATCH_RECORD set PHASE = :1 WHERE BATCH_ID = :2 LOB_DATA 1 32
TFR_REP SELECT LOADER as loader, LOADED_TIME as loadedTime, LOAD_STATUS as loadStatus, L LOB_DATA 1 32
TFR_REP SELECT LOADER as loader, LOADED_TIME as loadedTime, LOAD_STATUS as loadStatus, L LOB_DATA 1 32
TFR_REP SELECT LOADER as loader, LOADED_TIME as loadedTime, LOAD_STATUS as loadStatus, L LOB_DATA 1 32
TFR_REP INSERT INTO LOADER_CSV_LOAD_RECORD (CSV_DATE, LOAD_STATUS, LOAD_COMMENT, CSV_ID, LOB_DATA 1 32
TFR_REP SELECT LOADER as loader, LOADED_TIME as loadedTime, LOAD_STATUS as loadStatus, L LOB_DATA 1 32
TFR_REP UPDATE LOADER_REPORT_BATCH_RECORD set PHASE = :1 WHERE BATCH_ID = :2 LOB_DATA 1 32
TFR_REP SELECT VERSION FROM TF_DIMENSION_VERSION WHERE NAME = :1 LOB_DATA 1 32
TFR_REP UPDATE LOADER_REPORT_BATCH_RECORD set PHASE = :1 WHERE BATCH_ID = :2 LOB_DATA 1 32
USERNAME SQL_FULLTEXT SEGTYPE EXTENTS BLOCKS
------------------------------ -------------------------------------------------------------------------------- --------- ---------- ----------
TFR_REP UPDATE LOADER_REPORT_BATCH_RECORD set PHASE = :1 WHERE BATCH_ID = :2 LOB_DATA 1 32
TFR_REP SELECT VERSION FROM TF_DIMENSION_VERSION WHERE NAME = :1 LOB_DATA 1 32
TFR_REP SELECT LOADER as loader, LOADED_TIME as loadedTime, LOAD_STATUS as loadStatus, L LOB_DATA 1 32
TFR_REP select 1 from dual LOB_DATA 1 32
TFR_REP select 1 from dual LOB_DATA 1 32
TFR_REP UPDATE LOADER_REPORT_BATCH_RECORD set PHASE = :1 WHERE BATCH_ID = :2 LOB_DATA 1 32
TFR_REP UPDATE LOADER_REPORT_BATCH_RECORD set PHASE = :1 WHERE BATCH_ID = :2 LOB_DATA 1 32
TFR_REP SELECT LOADER, LOADED_TIME, LOAD_STATUS, LOAD_COMMENT FROM TFR_LOAD_RECORD LOB_DATA 1 32
I need your help & suggestions to overcome on this issue.
Regards,
Daulat
|
|
|
|
|
|
|
|
|
|
|
|
Re: ORA-1652: unable to extend temp segment by 32 in tablespace TEMP [message #668199 is a reply to message #668153] |
Mon, 12 February 2018 12:12 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
John Watson
Messages: 8944 Registered: January 2010 Location: Global Village
|
Senior Member |
|
|
v$sql_workarea will show the details of how much memory was used, with estimates of what is needed. I would guess that you are getting a hash join, and that is what is spilling to disc. Your outer join will be forcing the join order which may not help. If you hint a nested loop join, the memory requirement will drop hugely for the join (though no for the sort).
|
|
|
|