1 2 Previous Next 26 Replies Latest reply: Dec 7, 2012 11:54 AM by jgarry RSS

    Temp tablespace full

    CrazyAnie
      Hi All,

      I am not able to execute any select queries contaning huge sort operation on my DB as I am getting the below mentioned error:

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

      I can see that of all the 1200 gigs allocated to this tablespace, 0% are free. But even after logging out of that session and relogging, the TEMP tablespace remains at the same capacity. So, do i now need to bounce the database so as to free it?

      Also, when i check the v$sort_segment and v$sort_usage views, i find both of them empty. Then why is not the space getting deallocated. Am in following the wrong trail? Please advice.
        • 1. Re: Temp tablespace full
          635471
          CrazyAnie wrote:
          Hi All,
          I can see that of all the 1200 gigs allocated to this tablespace, 0% are free. But even after logging out of that session and relogging, the TEMP tablespace remains at the same capacity. So, do i now need to bounce the database so as to free it?

          Also, when i check the v$sort_segment and v$sort_usage views, i find both of them empty. Then why is not the space getting deallocated. Am in following the wrong trail? Please advice.
          Temporary tablespace extents do not get deleted when they are no longer in use, they get re-used. You see the tablespace as 100% full, but as long as the sort usage shows that nothing is being used then the allocated extents will be available.
          • 2. Re: Temp tablespace full
            CrazyAnie
            Yes, a select on the v$sort_usage returns me no rows. But still i am getting the same ORA-01652 error.
            • 3. Re: Temp tablespace full
              Deepak_DBA
              hi,

              which Oralce version and O/S ..?

              try increase the temp tablespace size..

              regards,
              Deepak
              • 4. Re: Temp tablespace full
                CrazyAnie
                The DB is Oracle 10g R2 and the OS is Solaris 10. I tried increasing the TEMP tablespace from 1 gig to 1.5 gigs. But still I am getting the same error. The TEMP tablespace again increases to 100%.
                • 5. Re: Temp tablespace full
                  635471
                  Have a look at the query explain plan and see what the estimated size of the sort operation is.
                  • 6. Re: Temp tablespace full
                    user503699
                    Did you try querying V$TEMPSEG_USAGE view to see who is using TEMP tablespace?
                    • 7. Re: Temp tablespace full
                      CrazyAnie
                      Yes, the V$TEMPSEG_USAGE does not return me any rows. Also, when I try to execute the query on SQLPLUS, I am able to retrieve the results. But when I try running the same query through Business Objects using the Custom SQL option, i get the ORA-01652 error.
                      • 8. Re: Temp tablespace full
                        635471
                        What's the query? How much temp space is it expected to take up?
                        • 9. Re: Temp tablespace full
                          Deepak_DBA
                          hi,

                          From DBA Exprt Brain's Peasland suggestion.

                          The ORA-1652 error simply states that your tablespace does not have enough free space to satisfy a request for a new extent. So you'll have to add more space or figure out why you don't have enough free space ther already. You didn't give me much more to go on to help you so I'll just add some more information. Hopefully, one of these pieces will assist you. First, a 1 or 2GB TEMP tablespace is not that particularly large in today's environments. I've seen TEMP tablespaces of 10GB or more. So it may be that you simply don't have a large enough TEMP tablespace to support all of your sorting needs. Second, there is a bug in Oracle 8i and 9i when using Locally Managed Tablespaces (LMTs) for TEMP tablespaces when the applications maintain persistent connections, that the instance does not release unused temporary segments for future use by another process. The way to correct this problem is to revert back to Dictionary Managed Tablespaces for TEMP.

                          regards,
                          Deepak
                          • 10. Re: Temp tablespace full
                            695836
                            Create another temporary tablespace with approriate size and options and make it default.
                            • 11. Re: Temp tablespace full
                              635471
                              CrazyAnie wrote:
                              The DB is Oracle 10g R2 and the OS is Solaris 10. I tried increasing the TEMP tablespace from 1 gig to 1.5 gigs. But still I am getting the same error. The TEMP tablespace again increases to 100%.
                              You said before that the temp tablespace was 1200 gb. Which is it?
                              • 12. Re: Temp tablespace full
                                CrazyAnie
                                The query is a simple one and does not take more than a few secs to retrieve the results in Oracle. But my concern area is that if the V$TEMPSEG_USAGE is empty, why is the TEMP tablespace at 100% capacity. Would bouncing the database be the only option available?
                                • 13. Re: Temp tablespace full
                                  635471
                                  CrazyAnie wrote:
                                  The query is a simple one and does not take more than a few secs to retrieve the results in Oracle.
                                  So when does this error occur if the query completes?
                                  But my concern area is that if the V$TEMPSEG_USAGE is empty, why is the TEMP tablespace at 100% capacity.
                                  What query or tool tells you that it is at 100% capacity?
                                  • 14. Re: Temp tablespace full
                                    CrazyAnie
                                    Hi,
                                    Below is the query I am using to check the free and available space on a tablespace:

                                    SELECT /* + RULE */ df.tablespace_name "Tablespace",
                                    df.bytes / (1024 * 1024) "Size (MB)",
                                    SUM(fs.bytes) / (1024 * 1024) "Free (MB)",
                                    Nvl(Round(SUM(fs.bytes) * 100 / df.bytes),1) "% Free",
                                    Round((df.bytes - SUM(fs.bytes)) * 100 / df.bytes) "% Used"
                                    FROM dba_free_space fs,
                                    (SELECT tablespace_name,SUM(bytes) bytes
                                    FROM dba_data_files
                                    GROUP BY tablespace_name) df
                                    WHERE fs.tablespace_name (+) = df.tablespace_name
                                    GROUP BY df.tablespace_name,df.bytes
                                    UNION ALL
                                    SELECT /* + RULE */ df.tablespace_name tspace,
                                    fs.bytes / (1024 * 1024),
                                    SUM(df.bytes_free) / (1024 * 1024),
                                    Nvl(Round((SUM(fs.bytes) - df.bytes_used) * 100 / fs.bytes), 1),
                                    Round((SUM(fs.bytes) - df.bytes_free) * 100 / fs.bytes)
                                    FROM dba_temp_files fs,
                                    (SELECT tablespace_name,bytes_free,bytes_used
                                    FROM v$temp_space_header
                                    GROUP BY tablespace_name,bytes_free,bytes_used) df
                                    WHERE fs.tablespace_name (+) = df.tablespace_name
                                    GROUP BY df.tablespace_name,fs.bytes,df.bytes_free,df.bytes_used
                                    ORDER BY 4 DESC;

                                    This query retrieves me the used space on the TEMP segment to be 100%.
                                    1 2 Previous Next