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

FREESPACE SCRIPT

762809 Newbie
Currently Being Moderated
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... Guru
    Currently Being Moderated
    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 Oracle ACE
    Currently Being Moderated
    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 Oracle ACE
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Journeyer
    Currently Being Moderated
    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... Guru
    Currently Being Moderated
    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 Journeyer
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    4 of them are not shown because your code is incorrect.

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

    Yes sir.Thanks for pointing it out.


    Regards,
    Anand
  • 11. Re: Tablespace  Used/free space
    762809 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    I checked the code again.
    It is VERY easy to solve it.

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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points