Home » RDBMS Server » Server Administration » ORA-1652: unable to extend temp segment by 32 in tablespace TEMP (Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit , Solaris, )
ORA-1652: unable to extend temp segment by 32 in tablespace TEMP [message #668104] Wed, 07 February 2018 02:40 Go to next message
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

Report message to a moderator

Re: ORA-1652: unable to extend temp segment by 32 in tablespace TEMP [message #668105 is a reply to message #668104] Wed, 07 February 2018 02:50 Go to previous messageGo to next message
Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator

I can't help I'm blocked waiting for your feedback(s) in your previous topic(s).

Report message to a moderator

Re: ORA-1652: unable to extend temp segment by 32 in tablespace TEMP [message #668106 is a reply to message #668105] Wed, 07 February 2018 03:03 Go to previous messageGo to next message
daulat01
Messages: 62
Registered: May 2011
Location: Delhi
Member
Sorry Michel for not updating the previous topics but I will update all those very soon. So please help me now on this issue.

Report message to a moderator

Re: ORA-1652: unable to extend temp segment by 32 in tablespace TEMP [message #668111 is a reply to message #668106] Wed, 07 February 2018 03:26 Go to previous messageGo to next message
daulat01
Messages: 62
Registered: May 2011
Location: Delhi
Member
Hi Michel, Please give your suggestions on this issue. I have update the actions with solutions on many previous topics. I will do the needful very soon for rest of them also.

Report message to a moderator

Re: ORA-1652: unable to extend temp segment by 32 in tablespace TEMP [message #668112 is a reply to message #668111] Wed, 07 February 2018 03:44 Go to previous messageGo to next message
Flyby
Messages: 188
Registered: March 2011
Location: Belgium
Senior Member
Try to find out which query is using up temp-tablespace. Search on "what is using temp tablespace oracle". Example 1Example 2

Report message to a moderator

Re: ORA-1652: unable to extend temp segment by 32 in tablespace TEMP [message #668114 is a reply to message #668112] Wed, 07 February 2018 04:36 Go to previous messageGo to next message
daulat01
Messages: 62
Registered: May 2011
Location: Delhi
Member
Hi Flyby,

Thanks for your suggestion .Please see below the output of the query. For all the SEGTYPE is lob_data.

SQL> SELECT s.sid, s.username, u.tablespace, s.sql_hash_value||'/'||u.sqlhash hash_value, u.segtype, u.contents, u.blocks FROM v$session s, v$tempseg_usage u WHERE s.saddr=u.session_addr order by u.blocks;

SID USERNAME TABLESPACE HASH_VALUE SEGTYPE CONTENTS BLOCKS
---------- --------------- ---------- ---------------------------------------- --------- --------- ----------
6663 TFR_REP TEMP 0/3703601952 LOB_DATA TEMPORARY 32
12 TFR_REP TEMP 0/2286154903 LOB_DATA TEMPORARY 32
6607 TFR_REP TEMP 0/3549668025 LOB_DATA TEMPORARY 32
6603 TFR_REP TEMP 0/2298121006 LOB_DATA TEMPORARY 32
5739 TFR_REP TEMP 0/1309801644 LOB_DATA TEMPORARY 32
5728 TFR_REP TEMP 0/393902046 LOB_DATA TEMPORARY 32
5714 TFR_REP TEMP 0/659798301 LOB_DATA TEMPORARY 32
5704 TFR_REP TEMP 0/2286154903 LOB_DATA TEMPORARY 32
5688 TFR_REP TEMP 0/1207196392 LOB_DATA TEMPORARY 32
5687 TFR_REP TEMP 0/1417950549 LOB_DATA TEMPORARY 32
5681 TFR_REP TEMP 0/3448261671 LOB_DATA TEMPORARY 32
5665 TFR_REP TEMP 0/4276667616 LOB_DATA TEMPORARY 32
5656 TFR_REP TEMP 0/3838461641 LOB_DATA TEMPORARY 32
4784 TFR_REP TEMP 0/1207196392 LOB_DATA TEMPORARY 32
4756 TFR_REP TEMP 0/1370853761 LOB_DATA TEMPORARY 32
6611 TFR_REP TEMP 0/2057823357 LOB_DATA TEMPORARY 32
4713 TFR_REP TEMP 525930840/2866845384 LOB_DATA TEMPORARY 32
3852 TFR_REP TEMP 0/275400831 LOB_DATA TEMPORARY 32
3848 TFR_REP TEMP 0/3162019798 LOB_DATA TEMPORARY 32
3838 TFR_REP TEMP 0/1696281856 LOB_DATA TEMPORARY 32
3813 TFR_REP TEMP 0/1750456200 LOB_DATA TEMPORARY 32
3803 TFR_REP TEMP 0/1117825500 LOB_DATA TEMPORARY 32
2915 TFR_REP TEMP 0/266263100 LOB_DATA TEMPORARY 32
2902 TFR_REP TEMP 0/393902046 LOB_DATA TEMPORARY 32
2896 TFR_REP TEMP 0/2929653496 LOB_DATA TEMPORARY 32
2886 TFR_REP TEMP 0/3675397797 LOB_DATA TEMPORARY 32
2863 TFR_REP TEMP 0/3182788982 LOB_DATA TEMPORARY 32
2862 TFR_REP TEMP 0/500910927 LOB_DATA TEMPORARY 32
2852 TFR_REP TEMP 0/3152521020 LOB_DATA TEMPORARY 32
2840 TFR_REP TEMP 0/3338291751 LOB_DATA TEMPORARY 32
2832 TFR_REP TEMP 0/3645728958 LOB_DATA TEMPORARY 32
1966 TFR_REP TEMP 0/2929653496 LOB_DATA TEMPORARY 32
1962 TFR_REP TEMP 0/3495631859 LOB_DATA TEMPORARY 32
1961 TFR_REP TEMP 0/1370853761 LOB_DATA TEMPORARY 32
1933 TFR_REP TEMP 0/2067234886 LOB_DATA TEMPORARY 32
1932 TFR_REP TEMP 0/3110789709 LOB_DATA TEMPORARY 32
1905 TFR_REP TEMP 0/556224672 LOB_DATA TEMPORARY 32
1903 TFR_REP TEMP 0/3024844007 LOB_DATA TEMPORARY 32
1893 TFR_REP TEMP 0/275400831 LOB_DATA TEMPORARY 32
1887 TFR_REP TEMP 0/1114026510 LOB_DATA TEMPORARY 32
1026 TFR_REP TEMP 0/659798301 LOB_DATA TEMPORARY 32
1012 TFR_REP TEMP 0/3916456439 LOB_DATA TEMPORARY 32
992 TFR_REP TEMP 0/556224672 LOB_DATA TEMPORARY 32
983 TFR_REP TEMP 0/3838461641 LOB_DATA TEMPORARY 32
73 TFR_REP TEMP 0/3182788982 LOB_DATA TEMPORARY 32
70 TFR_REP TEMP 0/3838461641 LOB_DATA TEMPORARY 32
65 TFR_REP TEMP 0/1139569086 LOB_DATA TEMPORARY 32
62 TFR_REP TEMP 0/2037591373 LOB_DATA TEMPORARY 32
56 TFR_REP TEMP 0/361093446 LOB_DATA TEMPORARY 32
37 TFR_REP TEMP 0/3838461641 LOB_DATA TEMPORARY 32
27 TFR_REP TEMP 0/2929653496 LOB_DATA TEMPORARY 32
4737 TFR_REP TEMP 2023135342/2866845384 HASH TEMPORARY 1399168

52 rows selected.

SQL>

Report message to a moderator

Re: ORA-1652: unable to extend temp segment by 32 in tablespace TEMP [message #668115 is a reply to message #668114] Wed, 07 February 2018 05:07 Go to previous messageGo to next message
cookiemonster
Messages: 13959
Registered: September 2008
Location: Rainy Manchester
Senior Member
You need to find out what SQL statements are using the most temp and see what they are doing and if their temp usage can be reduced.

Report message to a moderator

Re: ORA-1652: unable to extend temp segment by 32 in tablespace TEMP [message #668153 is a reply to message #668115] Thu, 08 February 2018 23:08 Go to previous messageGo to next message
daulat01
Messages: 62
Registered: May 2011
Location: Delhi
Member
Hi ,

These are the queries which are consuming temp space.

select sql_text from v$sql where sql_id='czukn41aka2ym';
SQL_TEXT
------------------------------------------------------------------------------------------------------------------------------------
SELECT CATEGORY_ID, CATEGORY_DESC, NVL(ROOT_RELATION_ID, -1) AS ROOT_RELATION_ID FROM TFR_REP.TFR_CATEGORY_DIM C LEFT OUTER JOIN TFR_REP.TF_ROOT_RELATION R ON C.CATEGORY_ID = R.ROOT_ID WHERE CATEGORY_ID = 94 OR ROOT_RELATION_ID IS NOT NULL ORDER BY CATEGORY_ID


SQL> select SQL_text from v$sql where sql_id='dxurk7yt8q6st';

SQL_TEXT
------------------------------------------------------------------------------------------------------------------------------------
SELECT COUNT(TABLE_NAME) FROM USER_TABLES WHERE TABLE_NAME = :B1 AND PARTITIONED ='YES'
SELECT COUNT(TABLE_NAME) FROM USER_TABLES WHERE TABLE_NAME = :B1 AND PARTITIONED ='YES'


SQL> select SQL_text from v$sql where sql_id='7zvr49mbwdcv3';

SQL_TEXT
------------------------------------------------------------------------------------------------------------------------------------
SELECT COUNT (TABLE_NAME) FROM USER_TABLES WHERE TABLE_NAME = UPPER (:B1 )
SELECT COUNT (TABLE_NAME) FROM USER_TABLES WHERE TABLE_NAME = UPPER (:B1 )


Regards,
Daulat

Report message to a moderator

Re: ORA-1652: unable to extend temp segment by 32 in tablespace TEMP [message #668197 is a reply to message #668153] Mon, 12 February 2018 09:16 Go to previous messageGo to next message
Flyby
Messages: 188
Registered: March 2011
Location: Belgium
Senior Member
Contact the user of sql_id='czukn41aka2ym' (the one with TFR_REP). The one with user_table is less likely the culprit.

Report message to a moderator

Re: ORA-1652: unable to extend temp segment by 32 in tablespace TEMP [message #668198 is a reply to message #668153] Mon, 12 February 2018 09:24 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
daulat01 wrote on Thu, 08 February 2018 21:08
Hi ,

These are the queries which are consuming temp space.

select sql_text from v$sql where sql_id='czukn41aka2ym';
SQL_TEXT
------------------------------------------------------------------------------------------------------------------------------------
SELECT CATEGORY_ID, CATEGORY_DESC, NVL(ROOT_RELATION_ID, -1) AS ROOT_RELATION_ID FROM TFR_REP.TFR_CATEGORY_DIM C LEFT OUTER JOIN TFR_REP.TF_ROOT_RELATION R ON C.CATEGORY_ID = R.ROOT_ID WHERE CATEGORY_ID = 94 OR ROOT_RELATION_ID IS NOT NULL
ORDER BY CATEGORY_ID


The ORDER BY clause is most likely culprit.

Report message to a moderator

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 messageGo to next message
John Watson
Messages: 8960
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).

Report message to a moderator

Re: ORA-1652: unable to extend temp segment by 32 in tablespace TEMP [message #668255 is a reply to message #668199] Wed, 14 February 2018 22:36 Go to previous message
daulat01
Messages: 62
Registered: May 2011
Location: Delhi
Member
Thanks Johan , I am working on this & update the status once done.

Report message to a moderator

Previous Topic: OracleServiceTMTDB service terminated unexpectedly
Next Topic: ORA-14511: cannot perform operation on a partitioned object
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Thu Nov 28 11:45:08 CST 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.