This discussion is archived
1 2 Previous Next 26 Replies Latest reply: Dec 7, 2012 9:54 AM by jgarry RSS

Temp tablespace full

CrazyAnie Newbie
Currently Being Moderated
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 Expert
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Pro
    Currently Being Moderated
    hi,

    which Oralce version and O/S ..?

    try increase the temp tablespace size..

    regards,
    Deepak
  • 4. Re: Temp tablespace full
    CrazyAnie Newbie
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    Did you try querying V$TEMPSEG_USAGE view to see who is using TEMP tablespace?
  • 7. Re: Temp tablespace full
    CrazyAnie Newbie
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    What's the query? How much temp space is it expected to take up?
  • 9. Re: Temp tablespace full
    Deepak_DBA Pro
    Currently Being Moderated
    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 Journeyer
    Currently Being Moderated
    Create another temporary tablespace with approriate size and options and make it default.
  • 11. Re: Temp tablespace full
    635471 Expert
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points