Tablespaces size and free space

In this forum Oracle Applications DBA's, System Administrators & Developers can share their knowledge/issues.
Post Reply
admin
Posts: 2062
Joined: Fri Mar 31, 2006 12:59 am
Location: Pakistan
Contact:

Tablespaces size and free space

Post by admin »

select a.TABLESPACE_NAME "TableSpace Name",
round(a.BYTES / 1024 / 1024) "MB Allocated",
round((a.BYTES-nvl(b.BYTES, 0)) / 1024 / 1024) "MB Used",
nvl(round(b.BYTES / 1024 / 1024), 0) "MB Free",
round(((a.BYTES-nvl(b.BYTES, 0))/a.BYTES)*100,2) "Pct Used",
round((1-((a.BYTES-nvl(b.BYTES,0))/a.BYTES))*100,2) "% Free"
from (select TABLESPACE_NAME,
sum(BYTES) BYTES
from sys.dba_data_files
group by TABLESPACE_NAME) a,
(select TABLESPACE_NAME,
sum(BYTES) BYTES
from sys.dba_free_space
group by TABLESPACE_NAME) b
where a.TABLESPACE_NAME = b.TABLESPACE_NAME (+)
--And a.tablespace_name in ('XXX')
order by ((a.BYTES-b.BYTES)/a.BYTES) desc

-- Note : AutoExtend is not taken into account in this query.
Post Reply

Who is online

Users browsing this forum: No registered users and 4 guests