5 Replies Latest reply: Nov 13, 2007 9:13 PM by 558902 RSS

    Clear Temporary Tablespace

    512627
      hai All,


      How to clear the temporary tablespace.When shutdown the database it clears the temporary tablespace? .. any other options? ... Please help?


      Shiju
        • 1. Re: Clear Temporary Tablespace
          591186
          Temp tablespaces are not released automatically.

          Storage in TEMP Tablespace is not automatically released

          Temporary Tablespaces
          http://download.oracle.com/docs/cd/B19306_01/server.102/b14231/tspaces.htm#i1013552

          Create another temporary tablespace and assign it as default temporary tablespace and drop the old one.
          • 2. Re: Clear Temporary Tablespace
            ajallen
            How do you know there is anything in the temporary tablespace that needs to be 'clear'ed? Oracle allocates temporary segments to the temporary tablespace, but does not deallocate them when they are no longer needed. Instead they are freed so that other transactions can use them. This saves recursive calls to the data dictionary to allocate and later deallocate temporary segments. It may appear that the temporary tablespace is full, but this is not necessarily the case. If you want to see how temp space is being used at any particular moment, try a query something like this
            COLUMN user       FORMAT a20               HEADING 'User'
            COLUMN username   FORMAT a10               HEADING 'Username'
            COLUMN osuser     FORMAT a12               HEADING 'OS User'
            COLUMN sid_ser    FORMAT a10               HEADING 'SID,SER'
            COLUMN blk_mb     FORMAT 99,999            HEADING 'Temp MB'
            COLUMN sorts      FORMAT 9,999             HEADING 'Sorts'
            COLUMN sql_text   FORMAT a40 word_wrapped  HEADING 'SQL Text'
            COLUMN process    FORMAT a35 word_wrapped  HEADING 'Module:Action'
            COLUMN tablespace NOPRINT                new_value ts_name

            BREAK ON sid_ser SKIP 1 DUPLICATES
            BREAK ON tablespace -
                  ON REPORT
            COMPUTE SUM OF blk_mb on tablespace report

            TTITLE Center 'Current Temp Usage'   -
              skip Center '~~~~~~~~~~~~~~~~~~' -
              skip 1 Left   'Temp Tablespace: ' ts_name -
              skip 2 -

            SELECT /*+ ORDERED */
                   u.tablespace,
                   s.sid || ',' || s.serial# as sid_ser,
                   s.username,
                   s.osuser,
                   (SUM(u.blocks)*TO_NUMBER(a.value))/1048576 as blk_mb,
                   x.sql_text,
                   s.module || ':' || chr(10) ||'.  ' || s.action as process
              FROM v$sort_usage u
                   ,v$session S
                   ,v$sql X
                   ,v$parameter a
            WHERE s.saddr                = u.session_addr
               AND s.sql_address          = x.address
               AND s.sql_hash_value       = x.hash_value
               AND a.name                 = 'db_block_size'
            GROUP
                BY u.tablespace
                 , s.sid
                 , s.serial#
                 , s.username
                 , s.osuser
                 , a.value
                 , x.sql_text
                 , s.module
                 , s.action
            ORDER
                 BY u.tablespace
                  , s.sid
            /

            TTITLE off
            clear computes
            clear breaks                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
            • 3. Re: Clear Temporary Tablespace
              608238
              You can
              create temporary tablespace TEMP2 tempfile '/path/filename' size 500m;
              alter database default temporary tablespace TEMP2;
              drop tablespace TEMP;

              and put a limit on the files capacity

              alter database tempfile '/path/filename' autoextend on next 10m maxsize 2000m;
              • 4. Re: Clear Temporary Tablespace
                247514
                What's your Oracle version?

                Instead of asking how, can you elaborate a little bit as to why you want to clear the temporary tablespace?
                • 5. Re: Clear Temporary Tablespace
                  558902
                  Maybe you don't need to care about it?