1 2 Previous Next 26 Replies Latest reply on Dec 7, 2012 5:54 PM by jgarry Go to original post
      • 15. Re: Temp tablespace full

        I would suggest the below steps:-

        1. create a new temporary tablespace (TEMP2) with say 1GB size

        2. Make it as the default for the database.

        3. Drop the temp tablespace that was being used (big size) -- drop tablespace temp including contents; --> supposing temp as the name of the temporary tablespace

        NOTE:- Don't use " including contents and datafiles. "

        4. Recreate the temporary tablespace (temp) --> create temporary tablespace temp tempfile 'use/the/same/files/1' size xxxxM, 'use/the/same/files/2' size xxxxM;

        5. Drop the temp2 which you had made default. --> drop temporary tablespace temp2 including contents and datafiles;

        refer to smon not clearing my temp tablespace

        • 16. Re: Temp tablespace full
          CrazyAnie wrote:
          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%.
          "free" and "used" in the context of v$temp_space_header do not mean "currently free for new operations" and "currently used by an operation", they just indicate whether the space has been allocated to a temporary segment or not. When a segment is created in the temporary tablespace it can be in use or not in use.

          Check V$TEMPSEG_USAGE to see the true current temporary segment usage: http://download.oracle.com/docs/cd/B19306_01/server.102/b14237/dynviews_2164.htm#REFRN30285
          1 person found this helpful
          • 17. Re: Temp tablespace full
            Okay, so that means my query for determining the used and free space on all the tablespaces in a DB needs to be modified so as to reflect the actual values.
            • 18. Re: Temp tablespace full

              select tablespace_name,sum(bytes_used/1024/1024)  USEB_IN_MB,sum(bytes_free/1024/1024) FREE_IN_MB  from v$temp_space_header group by tablespace_name;
              • 19. Re: Temp tablespace full
                please check this
                to get know
                ->how many bytes used
                ->how many in total
                note 8192 is my db_block_size
                SQL> select tablespace_name,sum(bytes) total_bytes from dba_temp_files group by tablespace_name;
                TABLESPACE_NAME             TOTAL_BYTES
                ------------------------------ -----------
                TEMP                      28311552
                SQL> select tablespace,sum(blocks)*8192 used_bytes  from
                v$sort_usage group by tablespace;
                no rows selected
                1 person found this helpful
                • 20. Re: Temp tablespace full
                  Also you can join ( v$sort_usage.SESSION_ADDR = v$session.SADDR) to know which session is consuming more temp.
                  • 21. Re: Temp tablespace full
                    CrazyAnie wrote:
                    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.
                    Sounds like you should set a trace like alter system set events '1652 trace name errorstack level 12'; so you can look at the trace file and see what sql actually is getting executed. Just to be sure it is the same SQL as the one you tried.
                    • 22. Re: Temp tablespace full
                      Srini Chavali-Oracle
                      Pl see if MOS Doc 1025288.6 (How to Diagnose and Resolve UNABLE TO EXTEND Errors) can help

                      • 23. Re: Temp tablespace full
                        Yeah, all the space in the TEMP tablespace finally got frred up without requiring to bounce the database. We are able ti execute all the queries perfectly now. Thanks to all for all your help!! :)
                        • 24. Re: Temp tablespace full
                          Hi Crazy,

                          Can u please let me know what steps u follow to sort out the problem.
                          • 25. Re: Temp tablespace full
                            SELECT tablespace_name,
                            total_blocks*16/1024 as total_MB,
                            used_blocks*16/1024 as used_MB,
                            free_blocks*16/1024 as free_MB
                            FROM v$sort_segment;
                            • 26. Re: Temp tablespace full
                              Please check the dates of the thread you are replying to before replying. It is usually better not to revive old threads.
                              1 2 Previous Next