1 2 Previous Next 17 Replies Latest reply on Jan 16, 2009 6:26 PM by 3530 Go to original post
      • 15. Re: Temp space is not deallocating
        Did you ever set up the task to capture the v$sort_usage data? After the error occurs the temp space is free as the terminated processes are cleared up. You need the data capture.

        Also limit the tempfile sizes. That is allocate them at the maximum supported size and see what happens.

        HTH -- Mark D Powell --
        • 16. Re: Temp space is not deallocating
          Timur Akhmadeev
          Please show results of
          select segtype, sum(blocks) from v$tempseg_usage group by segtype order by 2 desc;
          select * from (
              select username, session_addr,session_num, sum(blocks) from v$tempseg_usage 
              group by username, session_addr,session_num
          order by 3 desc) where rownum<=10;
          Edited by: Timur Akhmadeev on Jan 16, 2009 6:55 PM
          • 17. Re: Temp space is not deallocating

            Do your database can have randon SQL queries from end users? (SELECT Queries).

            If some user have missed to write one join condition between two tables, it can inflate TEMP tablespace by cartesian product.

            Fact is you are receiving ORA-6512 error, and something is blowing up your TEMP tables, you need to monitor when it is growing. Or your application must be receiving ORA-6512 message, you need find out what exactly is causing this error.

            If you want to de-allocate space back to OS, You can recreate TEMP tablespace. Oracle is behaving as it is designed to.

            Dilip Patel.
            1 2 Previous Next