1 2 Previous Next 22 Replies Latest reply: Dec 27, 2013 1:29 PM by jgarry RSS

    Tablespace Freespace

      Hi All,

       

      11.2.0.1

       

      I got this script that produces "database freespace" report. I found this at google and it is very cool as it helps me identify when tablespace is running out of space or in "critical" condition.

      Obvoiusly this is for 9i since it does not include TEMP. My problem is it does not include the TEMP tablespace if is currently running out of space on its current TS allocation. How do I include TEMP in this script?

       

      Thanks,

      pK

       

      ==========================

      set pages 0

      set lines 120

      set feedback off

      set term off

      set echo off

      set verify off

      col total for 999999999999999

      col used for 999999999999999

      col free for 999999999999999

      spool freespc.lst

      select  sysdate,tbs.tablespace_name,

                  tot.bytes total,

                  tot.bytes-sum(nvl(fre.bytes,0)) used,

                  sum(nvl(fre.bytes,0)) free,

                  round((1-sum(nvl(fre.bytes,0))/tot.bytes)*100,2) pct,

                  decode(

                      greatest((1-sum(nvl(fre.bytes,0))/tot.bytes)*100, 90),

                      90, '', '*'

                  ) pct_warn

      from    dba_free_space fre,

                 (select tablespace_name, sum(bytes) bytes

                 from    dba_data_files

                 group by tablespace_name) tot,

                 dba_tablespaces tbs

      where   tot.tablespace_name    = tbs.tablespace_name

      and     fre.tablespace_name(+) = tbs.tablespace_name

      group by tbs.tablespace_name, tot.bytes, tot.bytes

      order by 6, 2  ;

      spool off

      ==================

       

      OUTPUT

      =======

      TABLESPACETOTAL BYTESUSEDFREE% USEDCRITICAL
      INDX26,214,4001,048,57625,165,8244
      XDB20,971,5201,048,57619,922,9445
      UNDOTBS1393,216,00042,205,184351,010,81610.73
      TOOLS10,485,7601,245,1849,240,57611.88
      DRSYS20,971,5202,752,51218,219,00813.13
      ODM20,971,5207,340,03213,631,48835
      SYSAUX1,080,033,280923,598,848156,434,43285.52
      EXAMPLE151,388,160143,851,5207,536,64095.02*
      USERS83,886,08079,757,3124,128,76895.08*
      SYSTEM849,346,560842,006,5287,340,03299.14*
        • 1. Re: Tablespace Freespace
          Girish Sharma

          Because free space in temp tablespace does not matter.  If you wish for temp tablespace then :

           

          add following for temp files -

           

          select sum(bytes)/1024 kbytes_alloc, tablespace_name

          from sys.dba_temp_files

          group by tablespace_name

           


          the new query will be

           


          select b.tablespace_name, tbs_size SizeMb, a.free_space FreeMb

          from

          (select tablespace_name, round(sum(bytes)/1024/1024 ,2) as free_space

          from dba_free_space group by tablespace_name) a,

          (select tablespace_name, sum(bytes)/1024/1024 as tbs_size

          from dba_data_files group by tablespace_name

          UNION

          select tablespace_name, sum(bytes)/1024/1024 tbs_size

          from dba_temp_files

          group by tablespace_name ) b

          where a.tablespace_name(+)=b.tablespace_name;


          Source:https://community.oracle.com/thread/507636


          Regards

          Girish Sharma

          • 2. Re: Tablespace Freespace
            Anar Godjaev

            Hi,

             

            Check script:  https://community.oracle.com/thread/1667796?start=0&tstart=0

             

            SELECT tsu.tablespace_name,
                   Ceil(tsu.used_mb)                            "size MB",
                   Decode(Ceil(tsf.free_mb), NULL, 0,
                                             Ceil(tsf.free_mb)) "free MB",
                   Decode(100 - Ceil(tsf.free_mb / tsu.used_mb * 100), NULL, 100,
                                                                       100 - Ceil(
                   tsf.free_mb / tsu.used_mb * 100))            "% used"
            FROM   (SELECT tablespace_name,
                           SUM(bytes) / 1024 / 1024 used_mb
                    FROM   dba_data_files
                    GROUP  BY tablespace_name
                    UNION ALL
                    SELECT tablespace_name
                           || '  **TEMP**',
                           SUM(bytes) / 1024 / 1024 used_mb
                    FROM   dba_temp_files
                    GROUP  BY tablespace_name) tsu,
                   (SELECT tablespace_name,
                           SUM(bytes) / 1024 / 1024 free_mb
                    FROM   dba_free_space
                    GROUP  BY tablespace_name
                    UNION ALL
                    SELECT tablespace_name,
                           tfs.allocated_space / 1024 / 1024 free_mb
                    FROM   dba_temp_free_space tfs) tsf
            WHERE  tsu.tablespace_name = tsf.tablespace_name(+)
            ORDER  BY 4

            Thank you

            • 3. Re: Tablespace Freespace
              Karan Kukreja

              Hi,

               

              You should not be worried too much about temp . Temp management is done automatically by Oracle. Are you getting the error :

               

              could not extend xxxx bytes of xyz object in tablespace temp ?

               

              regards

              • 4. Re: Tablespace Freespace

                Thanks all,

                 

                 

                "You should not be worried too much about temp . Temp management is done automatically by Oracle.

                 

                How can I not worry? even if it is manage auto  but if you do not have free physical disk space, you will still get error, right?   I believe it need to be monitored right?

                • 5. Re: Tablespace Freespace
                  Girish Sharma

                  >How can I not worry? even if it is manage auto  but if you do not have free physical disk space, you will still get error, right?   I believe it need to be monitored right?

                   

                  Space Allocation in a Temporary Tablespace

                  You can view the allocation and deallocation of space in a temporary tablespace sort segment using the V$SORT_SEGMENT view. The V$TEMPSEG_USAGE view identifies the current sort users in those segments.

                  When a sort operation that uses temporary space completes, allocated extents in the sort segment are not deallocated; they are just marked as free and available for reuse. The DBA_TEMP_FREE_SPACE view displays the total allocated and free space in each temporary tablespace. See "Viewing Space Usage for Temporary Tablespaces" for more information. You can manually shrink a locally managed temporary tablespace that has a large amount of unused space. See "Shrinking a Locally Managed Temporary Tablespace" for details.

                   

                  Managing Tablespaces

                   

                  So, you do not need to worry for free space in temp tablespace, never.

                   

                  Regards

                  Girish Sharma

                  • 6. Re: Tablespace Freespace

                    HI Girish, Anar, Karan,

                     

                    I run the new query and I got this output below.

                    Shall I assume that TEMP will always be 100%  used and 0 free? So I will not check the logical side but only the physical?

                     

                    Thanks,

                    pK

                     

                     

                    TABLESPACE_NAME

                    size MB

                    free MB

                    % used

                    UNDOTBS1

                    375

                    361

                    3

                    INDX

                    25

                    24

                    4

                    XDB

                    20

                    19

                    5

                    TOOLS

                    10

                    9

                    11

                    DRSYS

                    20

                    18

                    13

                    ODM

                    20

                    13

                    35

                    SYSAUX

                    1030

                    147

                    85

                    USERS

                    80

                    4

                    95

                    EXAMPLE

                    145

                    8

                    95

                    SYSTEM

                    810

                    7

                    99

                    TEMP **TEMP**

                    63

                    0

                    100

                    • 7. Re: Tablespace Freespace

                      Hi Girish,

                       

                      "So, you do not need to worry for free space in temp tablespace, never.



                      Is this true even if the physical disk where the TEMP tablespace resides in d:\ or /u01 is 100% full?

                      • 8. Re: Tablespace Freespace
                        Karan Kukreja

                        As you would have read the post by Mr. Girish above , it clearly mentions that post sort operation , nothing is wiped off but marked as free and available for reuse.

                         

                        That's the reason its 100% full , well almost everytime.

                         

                         

                        Regards

                        Karan

                        • 9. Re: Tablespace Freespace
                          Girish Sharma

                          >Shall I assume that TEMP will always be 100%  used and 0 free?

                          Yes

                          >So I will not check the logical side but only the physical?

                          No. 

                          You use the SHRINK SPACE clause of the ALTER TABLESPACE statement to shrink a temporary tablespace, or the SHRINK TEMPFILE clause of the ALTER TABLESPACE statement to shrink a specific temp file of a temporary tablespace. Shrinking frees as much space as possible while maintaining the other attributes of the tablespace or temp file. The optional KEEP clause defines a minimum size for the tablespace or temp file.

                           

                          Managing Tablespaces

                           

                          Regards

                          Girish Sharma

                          • 10. Re: Tablespace Freespace
                            Anar Godjaev

                             

                             

                            TABLESPACE_NAME

                            size MB

                            free MB

                            % used

                            UNDOTBS1

                            375

                            361

                            3

                            INDX

                            25

                            24

                            4

                            XDB

                            20

                            19

                            5

                            TOOLS

                            10

                            9

                            11

                            DRSYS

                            20

                            18

                            13

                            ODM

                            20

                            13

                            35

                            SYSAUX

                            1030

                            147

                            85

                            USERS

                            80

                            4

                            95

                            EXAMPLE

                            145

                            8

                            95

                            SYSTEM

                            810

                            7

                            99

                            TEMP **TEMP**

                            63

                            0

                            100

                             

                            Well, I don't see any problem there. Your temporary tablespace simply isn't required. This could be possible if you have enough memory to carry out the sort operation.

                            • 11. Re: Tablespace Freespace
                              jgarry

                              Just because a database hasn't been used much in the past, doesn't mean it won't be in the future.  A db with less data than system probably isn't very interesting.

                              • 12. Re: Tablespace Freespace

                                Thanks JQ,

                                 

                                Well it is just my test db , that is why.

                                • 13. Re: Tablespace Freespace

                                  Hi Girish & All,

                                   

                                  >So I will not check the logical side but only the physical?

                                  No.

                                  You use the SHRINK SPACE clause of the ALTER TABLESPACE statement to shrink a temporary tablespace, or the SHRINK TEMPFILE clause of the ALTER TABLESPACEstatement to shrink a specific temp file of a temporary tablespace. Shrinking frees as much space as possible while maintaining the other attributes of the tablespace or temp file. The optional KEEP clause defines a minimum size for the tablespace or temp file.



                                  I am really confused now, what if currently the database is processing or sorting so much big data and still using the TEMP and continually extending, How can I shrink the tempfile just to avoid the /u01 to get 100% full where the TEMP resides? Does this mean even if the database is currently using the TEMP and extending continually because it is sorting so much data, I can still shrink it?



                                  Thanks....

                                  • 14. Re: Tablespace Freespace
                                    sb92075

                                    IMO, the TEMP tablespace datafile should NEVER have AUTOEXTEND  enabled.

                                    1 2 Previous Next