5 Replies Latest reply on Apr 27, 2012 4:48 AM by mBk77

    ORA-01652: unable to extend temp segment by 128 in tablespace TEMP

    884659
    884659 Apr 26, 2012 9:53 PM
      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.


      select * from v$sort_segment;

      TABLESPACE_NAME SEGMENT_FILE SEGMENT_BLOCK EXTENT_SIZE CURRENT_USERS TOTAL_EXTENTS TOTAL_BLOCKS USED_EXTENTS USED_BLOCKS FREE_EXTENTS FREE_BLOCKS ADDED_EXTENTS EXTENT_HITS FREED_EXTENTS FREE_REQUESTS MAX_SIZE MAX_BLOCKS MAX_USED_SIZE MAX_USED_BLOCKS MAX_SORT_SIZE MAX_SORT_BLOCKS RELATIVE_FNO

      TEMP 0 0 128 6 32766 4194048 6 768 32760 4193280 0 1006121 0 0 32766 4194048 32766 4194048 32764 4193792 0


      select * from v$tempfile;

      FILE# CREATION_CHANGE# CREATION_TIME TS# RFILE# STATUS ENABLED BYTES BLOCKS CREATE_BYTES BLOCK_SIZE NAME

      1 3687 22-SEP-11 3 1 ONLINE READ WRITE 34359721984 4194302 1048576000 8192 /san/oradata/prod/temp01.dbf


      select * from dba_temp_files;
      FILE_NAME FILE_ID TABLESPACE_NAME BYTES BLOCKS STATUS RELATIVE_FNO AUTOEXTENSIBLE MAXBYTES MAXBLOCKS INCREMENT_BY USER_BYTES USER_BLOCKS

      /san/oradata/prod/temp01.dbf 1 TEMP 34359721984 4194302 ONLINE 1 YES 34359721984 4194302 32000 34357641216 4194048

      Edited by: 881656 on Apr 26, 2012 2:52 PM
      • 21522 Views
      • Tags:
        • 1. Re: ORA-01652: unable to extend temp segment by 128 in tablespace TEMP
          sb92075
          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
            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
              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
                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
                  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;