5 Replies Latest reply on Sep 29, 2010 12:57 AM by Srini Chavali-Oracle

    ORA-01652: unable to extend temp segment

    801142
      Hi All,

      I'm Faber, new in this forum and yep, this is my first post.

      As written in the title, I have a problem with ORA-01652. It happens when I tried to insert data into a table. The complete error message is: "ORA-01652: unable to extend temp segment by 128 in tablespace TEMP".

      Surprisingly, tablespace TEMP has two files. Both of them are extend-able, and each of them has 32 GB maximum size. The first one already used 75%, so left 7.8 GB space remaining, meanwhile the other one has never been used.

      Based on these facts I think insufficient space is not the reason for the error. If it's not, then what are the possible causes of the error? Need help on this.

      Thanks.
        • 1. Re: ORA-01652: unable to extend temp segment
          Salman Qureshi
          Hi,
          Welcome to the forums. Please provide the version of your database.

          Please paste the output of the following queries (don't forget to enclose the results of queries in CODE tag to make it more readable.
          select tablespace_name from dba_tablespaces where contents='TEMPORARY';
          
          select tablespace_name,file_name,bytes/1024/1024/1024 from dba_temp_files;
          
          select property_value from database_properties where property_name='DEFAULT_TEMP_TABLESPACE';
          Also write here the INSERT statement you use for the insertion into this table.


          Salman
          • 2. Re: ORA-01652: unable to extend temp segment
            435289
            this could be an issue with your tablespace containing the table where you are inserting the data. check it out and if needed add some more space to it.
            • 3. Re: ORA-01652: unable to extend temp segment
              801142
              I'm using Oracle 10g.

              This is the result of the query, displayed subsequently:
              TABLESPACE_NAME
              TEMP
              
              TABLESPACE_NAME,  FILE_NAME,              BYTES/1024/1024/1024
              TEMP,                      /LOS/los/TEMP1.dbf,  31.9990234375
              TEMP,                      /LOS/los/TEMP2.dbf,  31.9990234375
              
              PROPERTY_VALUE
              TEMP
              Unfortunately I don't have idea which query that cause the error. I have a stored procedure here which triggered by schedule, and it has lots of insert statement. Is there any way to locate which line that raise the error? Sori, another question.

              Thanks
              • 4. Re: ORA-01652: unable to extend temp segment
                Salman Qureshi
                Hi,
                You did not answer the question about the INSERT statement which is causing this.
                Use following query to find out which SQL and session is generating a lot of temp. (This is not written by me rather i got it from somewhere on internet, curtsy to, who wrote this)
                select a.sid,a.serial#,a.osuser,a.username,a.machine,a.module,c.sql_id,b.sql_text,c.SEGTYPE, c.EXTENTS "MB Taken",sysdate
                from v$session a, 
                v$sql b,
                v$tempseg_usage c
                where a.serial#=c.SESSION_NUM  
                Now, if you don't know when it will happen and all the time you can't monitor it, use following method to run this query every 5 minutes and dump the information in a table from where you can later query to find out who was generating a lot of temp
                create table log_temp_usage
                 (sid number,
                 serial number,
                osuser varchar2(100),
                 dbuser varchar2(30),
                 machine varchar2(100),
                 module varchar2(100),
                 sql_id varchar2(20),
                sql_text varchar2(4000),
                 segtype varchar2(50),
                 size_taken_in_mb number,
                 log_time date);
                
                
                create or replace procedure log_temp_usage_proc is
                begin
                insert into log_temp_usage_salman
                select a.sid,a.serial#,a.osuser,a.username,a.machine,a.module,c.sql_id,b.sql_text,c.SEGTYPE, c.EXTENTS "MB Taken",sysdate
                from v$session a, 
                v$sql b,
                v$tempseg_usage c
                where a.serial#=c.SESSION_NUM
                commit;
                end;
                /
                
                
                exec dbms_job.submit (:job,what=>'log_temp_usage_proc;',next_date=>sysdate,interval=>'sysdate+1/288')
                commit;
                Salman
                • 5. Re: ORA-01652: unable to extend temp segment
                  Srini Chavali-Oracle
                  Pl post details of OS and database versions.

                  Pl see if MOS Doc 1025288.6 (TROUBLESHOOTING GUIDE (TSG) : UNABLE TO EXTEND Errors) can help

                  HTH
                  Srini