This content has been marked as final.
Show 5 replies
-
1. Re: ORA-01652: unable to extend temp segment by 128 in tablespace TEMP
sb92075 Apr 26, 2012 10:02 PM (in response to 884659)881656 wrote:
How to resolve/troubleshoot the error ORA-01652: unable to extend temp segment by 128 in tablespace TEMP? It seems to me that tablespace TEMP (31 GB) is already quite large.bcm@bcm-laptop:~$ oerr ora 1652 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. bcm@bcm-laptop:~$
-
2. Re: ORA-01652: unable to extend temp segment by 128 in tablespace TEMP
jgarry Apr 26, 2012 10:21 PM (in response to 884659)MOS ORA-1652 Error Troubleshooting [ID 793380.1] is a good start.
Look at V$TEMPSEG_USAGE as explained in the note. Common reasons are creations of indices or a small pga target. I find the PGA Memory Usage Details in dbconsole pretty useful for the latter. Are you using automatic memory, which version/patch level are you on? -
3. Re: ORA-01652: unable to extend temp segment by 128 in tablespace TEMP
884659 Apr 26, 2012 11:19 PM (in response to jgarry)I'm very new to Oracle and don't have a metalink account yet. ASM is not used. Version 11gR2
select from v$temp_extent_pool;*
TABLESPACE_NAME FILE_ID EXTENTS_CACHED EXTENTS_USED BLOCKS_CACHED BLOCKS_USED BYTES_CACHED BYTES_USED RELATIVE_FNO
TEMP 1 32766 4 4194048 512 34357641216 4194304 1
select from v$tempseg_usage;*
USERNAME USER SESSION_ADDR SESSION_NUM SQLADDR SQLHASH SQL_ID TABLESPACE CONTENTS SEGTYPE SEGFILE# SEGBLK# EXTENTS BLOCKS SEGRFNO#
APPUSER1 APPUSER1 00000008512A00C0 58111 00 0 TEMP TEMPORARY LOB_DATA 1025 1875840 1 128 1
APPUSER1 APPUSER1 00000008512A00C0 58111 00 0 TEMP TEMPORARY LOB_INDEX 1025 1865216 1 128 1
APPUSER2 APPUSER2 000000085157B700 23836 00000008AECA50D0 983083645 g3ztq2wx9jamx TEMP TEMPORARY LOB_DATA 1025 2313344 1 128 1
APPUSER2 APPUSER2 000000087126CC58 13025 000000088FCF2790 2048411275 2x0956px1hjnb TEMP TEMPORARY LOB_DATA 1025 3672320 1 128 1
APPUSER3 APPUSER3 000000087142BBB8 6557 000000088FDB1280 1411915307 b2tfaata2h7jb TEMP TEMPORARY LOB_DATA 1025 1973760 1 128 1 -
4. Re: ORA-01652: unable to extend temp segment by 128 in tablespace TEMP
900442 Apr 27, 2012 3:46 AM (in response to 884659)Hi,
Looks like Temporary LOB's not freeing up the TEMP space. Disconnecting the sessions will release the free space. Wait for others to comment further.
Regards,
Raj -
5. Re: ORA-01652: unable to extend temp segment by 128 in tablespace TEMP
mBk77 Apr 27, 2012 4:48 AM (in response to 884659)Create a Temp tablespace group and add your existing temp TS and a new TS to the group.
CREATE TEMPORARY TABLESPACE temp01
TEMPFILE ‘/u02/oradata/temp01.dbf’ SIZE 500M
TABLESPACE GROUP tempgroup_a;
ALTER TABLESPACE temp01 TABLESPACE GROUP tempgroup_b;