3 Replies Latest reply: Aug 6, 2010 4:24 AM by Mohamed ELAzab RSS

    TEMPORARY TABLESPACE SIZE

    782482
      Hi ,

      The database version
      BANNER
      ----------------------------------------------------------------
      Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
      PL/SQL Release 10.2.0.4.0 - Production
      CORE     10.2.0.4.0     Production
      TNS for Solaris: Version 10.2.0.4.0 - Production
      NLSRTL Version 10.2.0.4.0 - Production

      When i query temporary tablespace size using dba_temp_files.i get the folowing output

      TABLESPACE_NAME BYTES/1024/1024/1024 MAXBYTES/1024/1024/1024 AUTOEXTENSIBLE
      ------------------------------ ---------------------- ----------------------- --------------
      TEMP1 0.09765625 31.9999847412109375 YES
      TEMP1 0.09765625 31.9999847412109375 YES
      TEMP1 0.09765625 31.9999847412109375 YES
      TEMP1 0.09765625 31.9999847412109375 YES
      TEMP1 0.09765625 31.9999847412109375 YES
      TEMP1 0.09765625 31.9999847412109375 YES
      TEMP1 31.9999847412109375 31.9999847412109375 YES
      TEMP1 31.9999847412109375 31.9999847412109375 YES
      TEMP1 31.9999847412109375 31.9999847412109375 YES
      TEMP1 31.9999847412109375 31.9999847412109375 YES
      TEMP1 31.9999847412109375 31.9999847412109375 YES
      TEMP1 31.9999847412109375 31.9999847412109375 YES
      TEMP1 31.9999847412109375 31.9999847412109375 YES
      TEMP1 0.09765625 31.9999847412109375 YES
      TEMP1 0.09765625 31.9999847412109375 YES
      TEMP1 0.09765625 31.9999847412109375 YES


      To look at the free space i queried v$temp_space_header

      TABLESPACE_NAME SUM(BYTES_USED)/1024/1024/1024 SUM(BYTES_FREE)/1024/1024/1024
      ------------------------------ ------------------------------ ------------------------------
      TEMP1 224.0086822509765625 0.8701171875



      I find only freespace 0.8 gb but in dba_temp_files i find still some tempfiles can be autoextended they have not reached the max point.


      Am confused can anyone help me?


      Thx
        • 1. Re: TEMPORARY TABLESPACE SIZE
          CKPT
          Hi,

          can you check user_bytes?
          select file_name,bytes/1024/1024,user_bytes/1024/1024,maxbytes/1024/1024,autoextensible from dba_temp_files;
          Thanks
          • 2. Re: TEMPORARY TABLESPACE SIZE
            Mohamed ELAzab
            hello,
            Please check this website:
            http://www.dbspecialists.com/files/presentations/temp_space.html
            Kind regards
            Mohamed
            Oracle DBA
            • 3. Re: TEMPORARY TABLESPACE SIZE
              Mohamed ELAzab
              Monitoring Temporary Space Usage

              Instead of waiting for a temporary tablespace to fill and for statements to fail, you can monitor temporary space usage in the database in real time. At any given time, Oracle can tell you about all of the database’s temporary tablespaces, sort space usage on a session basis, and sort space usage on a statement basis. All of this information is available from v$ views, and the queries shown in this section can be run by any database user with DBA privileges.

              Temporary Segments

              The following query displays information about all sort segments in the database. (As a reminder, we use the term “sort segment” to refer to a temporary segment in a temporary tablespace.) Typically, Oracle will create a new sort segment the very first time a sort to disk occurs in a new temporary tablespace. The sort segment will grow as needed, but it will not shrink and will not go away after all sorts to disk are completed. A database with one temporary tablespace will typically have just one sort segment.

              SELECT A.tablespace_name tablespace, D.mb_total,
              SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_used,
              D.mb_total - SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_free
              FROM v$sort_segment A,
              (
              SELECT B.name, C.block_size, SUM (C.bytes) / 1024 / 1024 mb_total
              FROM v$tablespace B, v$tempfile C
              WHERE B.ts#= C.ts#
              GROUP BY B.name, C.block_size
              ) D
              WHERE A.tablespace_name = D.name
              GROUP by A.tablespace_name, D.mb_total;
              nOTE
              Note:
              Please review the link i provided.

              Edited by: Mohamed ELAzab on Aug 6, 2010 12:24 PM