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
      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
        • 1. Re: ORA-01652: unable to extend temp segment by 128 in tablespace TEMP
          sb92075
          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
            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
              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
                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
                  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;