May 19, 2009 2:14 pm
I occasionally want to know what tablespaces I have defined, how big they are and how much free space is available.
Thanks to Praveen at http://www.expertsharing.com/2008/02/26/calculate-size-of-tablespace-free-space-of-a-tablespace/ for providing the following query, which makes this information available easily.
set pages 999;
set lines 132;
SELECT *
FROM
( SELECT
c.tablespace_name,
ROUND(a.bytes/1048576,2) MB_Allocated,
ROUND(b.bytes/1048576,2) MB_Free,
ROUND((a.bytes-b.bytes)/1048576,2) MB_Used,
ROUND(b.bytes/a.bytes * 100,2) tot_Pct_Free,
ROUND((a.bytes-b.bytes)/a.bytes,2) * 100 tot_Pct_Used
FROM
( SELECT
tablespace_name,
SUM(a.bytes) bytes
FROM
sys.DBA_DATA_FILES a
GROUP BY
tablespace_name
) a,
( SELECT
a.tablespace_name,
NVL(SUM(b.bytes),0) bytes
FROM
sys.DBA_DATA_FILES a,
sys.DBA_FREE_SPACE b
WHERE
a.tablespace_name = b.tablespace_name (+)
AND a.file_id = b.file_id (+)
GROUP BY
a.tablespace_name
) b,
sys.DBA_TABLESPACES c
WHERE
a.tablespace_name = b.tablespace_name(+)
AND a.tablespace_name = c.tablespace_name
)
WHERE
tot_Pct_Used >=0
ORDER BY
tablespace_name;
Posted by Mark Nelson
Categories: Uncategorized
Tags: database
You must be logged in to post a comment.
Mobile Site | Full Site
Get a free blog at WordPress.com Theme: WordPress Mobile Edition by Alex King.
[…] you want to get a feeling for how much free space you have, you might like to use the query in this post, which will produce results like those shown […]
By Purging old instance data from SOA/BPM 11g | RedStack on October 6, 2010 at 8:39 pm
Thanks a lot for this useful script. Very Very Handy.
By acveer on October 5, 2011 at 1:31 am
You’re welcome. Thanks for taking the time to comment.
By Mark Nelson on October 20, 2011 at 9:06 am