查看Oracle表空间使用情况

查看Oracle表空间使用情况
1、查询表空间的总容量
select a.TABLESPACE_NAME, sum(a.BYTES) / 1024 / 1024 as MB
from sys.dba_data_files a
group by a.TABLESPACE_NAME
2、查询表空间的空闲容量
select b.TABLESPACE_NAME,
count(1) as extends,
sum(b.BYTES) / 1024 / 1024 as MB,
sum(b.BLOCKS) as blocks
from sys.dba_free_space b
group by b.TABLESPACE_NAME
3、查询表空间的使用情况
复制代码
select total.TABLESPACE_NAME,
round(total.MB, 2) as TOTAL_MB,
round(total.MB - free.MB, 2) as USED_MB,
ROUND((1 - free.MB / total.MB) * 100, 2) || ‘%’ as USED_PCT,
ROUND(free.MB, 2) as FREE_mb
from (select a.TABLESPACE_NAME, sum(a.BYTES) / 1024 / 1024 as MB
from sys.dba_data_files a
group by a.TABLESPACE_NAME) total,
(select b.TABLESPACE_NAME,
count(1) as extends,
sum(b.BYTES) / 1024 / 1024 as MB,
sum(b.BLOCKS) as blocks
from sys.dba_free_space b
group by b.TABLESPACE_NAME) free
where total.TABLESPACE_NAME = free.TABLESPACE_NAME