1 2 3 Previous Next 44 Replies Latest reply: Nov 17, 2010 2:41 AM by Niall Litchfield RSS

    FREESPACE SCRIPT

    762809
      Hi Friends,

      Oracle DB 9.2.0.6

      I got this freespace script from google:
      rem -----------------------------------------------------------------------
      rem Filename:   tsspace.sql
      rem Purpose:    Show Used/free space in Meg by tablespace name
      rem -----------------------------------------------------------------------
       
      tti "Space Usage for Database in Meg"
       
      SELECT Total.name "Tablespace Name",
             nvl(Free_space, 0) Free_space,
             nvl(total_space-Free_space, 0) Used_space, 
             total_space
      FROM
        (select tablespace_name, sum(bytes/1024/1024) Free_Space
           from sys.dba_free_space
          group by tablespace_name
        ) Free,
        (select b.name,  sum(bytes/1024/1024) TOTAL_SPACE
           from sys.v_$datafile a, sys.v_$tablespace B
          where a.ts# = b.ts#
          group by b.name
        ) Total
      WHERE Free.Tablespace_name(+) = Total.name
      ORDER BY Total.name
      / 
       
      tti off
      I run this script in our database and I got this output:
      Tablespace Name                FREE_SPACE USED_SPACE TOTAL_SPACE
      ------------------------------ ---------- ---------- -----------
      APPS_TS_ARCHIVE                         0          0     856.875
      APPS_TS_INTERFACE                       0          0     1210.25
      APPS_TS_MEDIA                        22.5     1177.5        1200
      APPS_TS_NOLOGGING                       0          0     680.875
      APPS_TS_QUEUES                    295.125   4172.875        4468
      APPS_TS_SEED                        253.5     2246.5        2500
      APPS_TS_SUMMARY                   193.125    806.875        1000
      APPS_TS_TX_DATA                         0          0   18941.625
      APPS_TS_TX_IDX                          0          0    9832.125
      APPS_UNDOTS1                    1861.8125   138.1875        2000
      CTXD                            2.3828125    21.3125  23.6953125
      ODM                               90.3125     9.6875         100
      OLAP                                   85         15         100
      OWAPUB                           9.921875    .078125          10
      PORTAL                           99.53125     .46875         100
      SYSTEM                         5829.52344    9276.25  15105.7734
       
      16 rows selected.
      Why is that the following tablespac has "0" freespace and "0" used space?
      APPS_TS_ARCHIVE                         0          0     856.875
      APPS_TS_INTERFACE                       0          0     1210.25
      APPS_TS_NOLOGGING                       0          0     680.875
      APPS_TS_TX_DATA                         0          0   18941.625
      APPS_TS_TX_IDX                          0          0    9832.125
      Thanks a lot,

      Ms K

      Edited by: user_unlimited on Oct 15, 2010 7:51 AM
        • 1. Re: Tablespace  Used/free space
          Anand...
          Hi,

          What is the oracle database version? Paste the output of below
          col tspace format a30
          colu tot_ts_size format 99999999.999
          colu free_ts_size format 99999999.999
          colu used_ts_size format 99999999.999
          select df.tablespace_name tspace,
              df.bytes/(1024*1024) tot_ts_size,
            (df.bytes/(1024*1024) -sum(fs.bytes)/(1024*1024)) Used_ts_size,
            sum(fs.bytes)/(1024*1024) free_ts_size,
                  round(sum(fs.bytes)*100/df.bytes) free_pct,
            round((df.bytes-sum(fs.bytes))*100/df.bytes) used_pct1
          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 
           order by 1
          /
          If on 10g check DBA_TABLESPACE_USAGE_METRICS

          Anand
          • 2. Re: Tablespace  Used/free space
            Helios-GunesEROL
            Hi user;

            Please check my blog there are some script avaliable as:

            * Oracle Alert.log file check script
            * Tablespace check script for Oracle E-Business Suite R11&R12
            * Cold-backup scripts for Oracle E-Business Suite R11&R12
            * Free Space Issues for Filesystems
            * Memory Monitor script

            http://heliosguneserol.wordpress.com/category/scripts-for-oracle-e-business-suite/

            Regard
            Helios
            • 3. Re: Tablespace  Used/free space
              Helios-GunesEROL
              Hi again;

              If you get to info about tablespace in addition to my script you could check below site:

              http://vsbabu.org/oracle/sect03.html
              http://www.oracle-base.com/dba/DBACategories.php

              Regard
              Helios
              • 4. Re: Tablespace  Used/free space
                762809
                thanks anand,

                I got this output:
                TSPACE                           TOT_TS_SIZE  USED_TS_SIZE  FREE_TS_SIZE   FREE_PCT  USED_PCT1
                ------------------------------ ------------- ------------- ------------- ---------- ----------
                APPS_TS_MEDIA                       1200.000      1177.500        22.500          2         98
                APPS_TS_QUEUES                      4468.000      4180.125       287.875          6         94
                APPS_TS_SEED                        2500.000      2246.500       253.500         10         90
                APPS_TS_SUMMARY                     1000.000       806.875       193.125         19         81
                APPS_TS_TX_DATA                    18953.625     18950.000         3.625          0        100
                APPS_UNDOTS1                        2000.000       113.188      1886.813         94          6
                CTXD                                  23.695        21.313         2.383         10         90
                ODM                                  100.000         9.688        90.313         90         10
                OLAP                                 100.000        15.000        85.000         85         15
                OWAPUB                                10.000          .078         9.922         99          1
                PORTAL                               100.000          .469        99.531        100          0
                SYSTEM                             13526.664      7704.641      5822.023         43         57
                
                12 rows selected.
                Why is that the others are not shown? there should be 16 lines?

                By the way APPS_TS_TX_DATA has been 100% for sometime but I did not get any error yet.
                Why is that?


                Thansk
                • 5. Re: Tablespace  Used/free space
                  sybrand_b
                  The script posted by Anand is not correct, as there is an inner join.

                  This script I downloaded from OTN this morning (I did use the search function) should be correct, after I debugged it
                  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
                  --------------------
                  Sybrand Bakker
                  Senior Oracle DBA
                  • 6. Re: FREESPACE SCRIPT
                    Niall Litchfield
                    user_unlimited wrote:
                    Hi Friends,

                    Oracle DB 9.2.0.6

                    I got this freespace script from google:
                    hmm, not always a great plan
                    rem -----------------------------------------------------------------------
                    rem Filename:   tsspace.sql
                    rem Purpose:    Show Used/free space in Meg by tablespace name
                    rem -----------------------------------------------------------------------
                    
                    tti "Space Usage for Database in Meg"
                    
                    SELECT Total.name "Tablespace Name",
                    nvl(Free_space, 0) Free_space,
                    nvl(total_space-Free_space, 0) Used_space, 
                    total_space
                    FROM
                    (select tablespace_name, sum(bytes/1024/1024) Free_Space
                    from sys.dba_free_space
                    group by tablespace_name
                    ) Free,
                    (select b.name,  sum(bytes/1024/1024) TOTAL_SPACE
                    from sys.v_$datafile a, sys.v_$tablespace B
                    where a.ts# = b.ts#
                    group by b.name
                    ) Total
                    WHERE Free.Tablespace_name(+) = Total.name
                    ORDER BY Total.name
                    / 
                    
                    tti off
                    I run this script in our database and I got this output:
                    Tablespace Name                FREE_SPACE USED_SPACE TOTAL_SPACE
                    ------------------------------ ---------- ---------- -----------
                    APPS_TS_ARCHIVE                         0          0     856.875
                    APPS_TS_INTERFACE                       0          0     1210.25
                    APPS_TS_MEDIA                        22.5     1177.5        1200
                    APPS_TS_NOLOGGING                       0          0     680.875
                    APPS_TS_QUEUES                    295.125   4172.875        4468
                    APPS_TS_SEED                        253.5     2246.5        2500
                    APPS_TS_SUMMARY                   193.125    806.875        1000
                    APPS_TS_TX_DATA                         0          0   18941.625
                    APPS_TS_TX_IDX                          0          0    9832.125
                    APPS_UNDOTS1                    1861.8125   138.1875        2000
                    CTXD                            2.3828125    21.3125  23.6953125
                    ODM                               90.3125     9.6875         100
                    OLAP                                   85         15         100
                    OWAPUB                           9.921875    .078125          10
                    PORTAL                           99.53125     .46875         100
                    SYSTEM                         5829.52344    9276.25  15105.7734
                    
                    16 rows selected.
                    Why is that the following tablespac has "0" freespace and "0" used space?
                    APPS_TS_ARCHIVE                         0          0     856.875
                    APPS_TS_INTERFACE                       0          0     1210.25
                    APPS_TS_NOLOGGING                       0          0     680.875
                    APPS_TS_TX_DATA                         0          0   18941.625
                    APPS_TS_TX_IDX                          0          0    9832.125
                    The key to understanding this is that a tablespace will not appear in DBA_FREE_SPACE if it is full, i.e there is no free space.

                    Your query outer joins free_space to the tablespace size statistics. This will result in a null value for any tablespace which doesn't appear in DBA_FREE_SPACE at all. Then at the top of the script you have
                     SELECT Total.name "Tablespace Name",
                            nvl(Free_space, 0) Free_space,
                            nvl(total_space-Free_space, 0) Used_space, 
                            total_space
                    .....
                    Well because free_space is null for a full tablespace then the NVL commands above will show a tablespace as having zero free and used space when it is in fact full. Not what you would expect from a free_space script. I suspect that you should amend this script to read as shown below. Still at least this one does have an outer join, so many of them don't.
                     SELECT Total.name "Tablespace Name",
                            nvl(Free_space, 0) Free_space,
                            total_space-nvl(Free_space, 0) Used_space, 
                            total_space
                    ....
                    >
                    Thanks a lot,

                    Ms K

                    Edited by: user_unlimited on Oct 15, 2010 7:51 AM
                    • 7. Re: Tablespace  Used/free space
                      Anand...
                      Why is that the others are not shown? there should be 16 lines?
                      4 for them are not shown because they are already 100% full (FREE_TS_SIZE = 0).What error are you expecting?Why not add datafile or resize the existing one, before getting error.

                      Anand
                      • 8. Re: Tablespace  Used/free space
                        Niall Litchfield
                        user_unlimited wrote:
                        thanks anand,

                        I got this output:
                        TSPACE                           TOT_TS_SIZE  USED_TS_SIZE  FREE_TS_SIZE   FREE_PCT  USED_PCT1
                        ------------------------------ ------------- ------------- ------------- ---------- ----------
                        APPS_TS_MEDIA                       1200.000      1177.500        22.500          2         98
                        APPS_TS_QUEUES                      4468.000      4180.125       287.875          6         94
                        APPS_TS_SEED                        2500.000      2246.500       253.500         10         90
                        APPS_TS_SUMMARY                     1000.000       806.875       193.125         19         81
                        APPS_TS_TX_DATA                    18953.625     18950.000         3.625          0        100
                        APPS_UNDOTS1                        2000.000       113.188      1886.813         94          6
                        CTXD                                  23.695        21.313         2.383         10         90
                        ODM                                  100.000         9.688        90.313         90         10
                        OLAP                                 100.000        15.000        85.000         85         15
                        OWAPUB                                10.000          .078         9.922         99          1
                        PORTAL                               100.000          .469        99.531        100          0
                        SYSTEM                             13526.664      7704.641      5822.023         43         57
                        
                        12 rows selected.
                        Why is that the others are not shown? there should be 16 lines?
                        There needs to be an outer join see my reply and Sybrand's above.

                        >
                        By the way APPS_TS_TX_DATA has been 100% for sometime but I did not get any error yet.
                        Why is that?
                        Almost certainly because the datafiles for APPS_TS_TX_DATA are set to autoextend and the script (indeed most scripts) doesn't take any account of that. There is no free space (so the file doesn't appear in dba_free_space) but the file can extend as needed. If this is the case then you need to do one of 2 things.

                        1) start monitoring against the autoextend maxsize (either set explicitly or block size dependent - should be 32gb for an apps database with the standard 8k block size). This requires even more careful logic than the previous scripts since you also have to bear in mind available disk capacity which may be less than the theoretical maxsize of the datafiles
                        2) create your datafiles with fixed sizes and non-autoextensible.
                        • 9. Re: Tablespace  Used/free space
                          sybrand_b
                          4 of them are not shown because your code is incorrect.

                          ------------
                          Sybrand Bakker
                          Senior Oracle DBA
                          • 10. Re: Tablespace  Used/free space
                            Anand...
                            Hi,

                            Yes sir.Thanks for pointing it out.


                            Regards,
                            Anand
                            • 11. Re: Tablespace  Used/free space
                              762809
                              thanks all....but
                              SQL> select   tsu.tablespace_name, ceil(tsu.used_mb) "size MB"
                                2  ,  decode(ceil(tsf.free_mb), NULL,0,ceil(tsf.free_mb)) "free MB"
                                3  ,  decode(100 - ceil(tsf.free_mb/tsu.used_mb*100), NULL, 100,
                                4                 100 - ceil(tsf.free_mb/tsu.used_mb*100)) "% used"
                                5  from  (select tablespace_name, sum(bytes)/1024/1024 used_mb
                                6    from   dba_data_files group by tablespace_name union all
                                7    select   tablespace_name || '  **TEMP**'
                                8    ,  sum(bytes)/1024/1024 used_mb
                                9    from   dba_temp_files group by tablespace_name) tsu
                               10  ,  (select tablespace_name, sum(bytes)/1024/1024 free_mb
                               11    from   dba_free_space group by tablespace_name
                               12    union all
                               13    select tablespace_name, tfs.ALLOCATED_SPACE)/1024/1024 free_mb
                               14    from dba_temp_free_space tfs
                               15    ) tsf
                               16  where  tsu.tablespace_name = tsf.tablespace_name (+)
                               17  order  by 4;
                                select tablespace_name, tfs.ALLOCATED_SPACE)/1024/1024 free_mb
                                                                           *
                              ERROR at line 13:
                              ORA-00923: FROM keyword not found where expected
                              Please check the code again :(


                              Thanks
                              • 12. Re: Tablespace  Used/free space
                                762809
                                thanks all....but
                                SQL> select   tsu.tablespace_name, ceil(tsu.used_mb) "size MB"
                                  2  ,  decode(ceil(tsf.free_mb), NULL,0,ceil(tsf.free_mb)) "free MB"
                                  3  ,  decode(100 - ceil(tsf.free_mb/tsu.used_mb*100), NULL, 100,
                                  4                 100 - ceil(tsf.free_mb/tsu.used_mb*100)) "% used"
                                  5  from  (select tablespace_name, sum(bytes)/1024/1024 used_mb
                                  6    from   dba_data_files group by tablespace_name union all
                                  7    select   tablespace_name || '  **TEMP**'
                                  8    ,  sum(bytes)/1024/1024 used_mb
                                  9    from   dba_temp_files group by tablespace_name) tsu
                                 10  ,  (select tablespace_name, sum(bytes)/1024/1024 free_mb
                                 11    from   dba_free_space group by tablespace_name
                                 12    union all
                                 13    select tablespace_name, tfs.ALLOCATED_SPACE)/1024/1024 free_mb
                                 14    from dba_temp_free_space tfs
                                 15    ) tsf
                                 16  where  tsu.tablespace_name = tsf.tablespace_name (+)
                                 17  order  by 4;
                                  select tablespace_name, tfs.ALLOCATED_SPACE)/1024/1024 free_mb
                                                                             *
                                ERROR at line 13:
                                ORA-00923: FROM keyword not found where expected
                                Please check the code again :(


                                Thanks
                                • 13. Re: Tablespace  Used/free space
                                  762809
                                  thanks all....but
                                  SQL> select   tsu.tablespace_name, ceil(tsu.used_mb) "size MB"
                                    2  ,  decode(ceil(tsf.free_mb), NULL,0,ceil(tsf.free_mb)) "free MB"
                                    3  ,  decode(100 - ceil(tsf.free_mb/tsu.used_mb*100), NULL, 100,
                                    4                 100 - ceil(tsf.free_mb/tsu.used_mb*100)) "% used"
                                    5  from  (select tablespace_name, sum(bytes)/1024/1024 used_mb
                                    6    from   dba_data_files group by tablespace_name union all
                                    7    select   tablespace_name || '  **TEMP**'
                                    8    ,  sum(bytes)/1024/1024 used_mb
                                    9    from   dba_temp_files group by tablespace_name) tsu
                                   10  ,  (select tablespace_name, sum(bytes)/1024/1024 free_mb
                                   11    from   dba_free_space group by tablespace_name
                                   12    union all
                                   13    select tablespace_name, tfs.ALLOCATED_SPACE)/1024/1024 free_mb
                                   14    from dba_temp_free_space tfs
                                   15    ) tsf
                                   16  where  tsu.tablespace_name = tsf.tablespace_name (+)
                                   17  order  by 4;
                                    select tablespace_name, tfs.ALLOCATED_SPACE)/1024/1024 free_mb
                                                                               *
                                  ERROR at line 13:
                                  ORA-00923: FROM keyword not found where expected
                                  Please check the code again :(


                                  Thanks
                                  • 14. Re: Tablespace  Used/free space
                                    sybrand_b
                                    I checked the code again.
                                    It is VERY easy to solve it.

                                    ------------
                                    Sybrand Bakker
                                    Senior Oracle DBA
                                    1 2 3 Previous Next