check free space in a tablespace
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: check free space in a tablespace

  1. #1
    Join Date
    Mar 2001
    Posts
    286
    I always use the following script to check how much free space left in the tablespaces.

    Question and Concern:

    Yesterday, I ran into a problem. If a tablespace is 100% full, it will not be included in dba_free_space view. Then, the script will completely ignore that tablespace. Does anyone have a good fix for this problem? Or, do you have a better script to check free space inside every tablespace?


    ----------------
    select d.tablespace_name,
    d.total/1048576 "Total (MB)",
    round(f.free/1048576,2) " Free",
    round(f.free/d.total*100,2) "% Free"
    from
    (select tablespace_name,
    round(sum(bytes)) free
    from dba_free_space
    group by tablespace_name
    ) f,
    (select tablespace_name, sum(bytes) total
    from dba_data_files
    group by tablespace_name) d
    WHERE d.tablespace_name = f.tablespace_name
    order by 1;

  2. #2
    Join Date
    Jul 2002
    Location
    Washington DC
    Posts
    110
    I hope following should work....(outer join)

    select d.tablespace_name,
    d.total/1048576 "Total (MB)",
    round(f.free/1048576,2) " Free",
    round(f.free/d.total*100,2) "% Free"
    from
    (select tablespace_name,
    round(sum(bytes)) free
    from dba_free_space
    group by tablespace_name
    ) f,
    (select tablespace_name, sum(bytes) total
    from dba_data_files
    group by tablespace_name) d
    WHERE d.tablespace_name = f.tablespace_name(+)
    order by 1;

    Manoj

  3. #3
    Join Date
    Mar 2001
    Posts
    286
    Excellent!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width