Oracle入门精读35-显示当前数据库的表空间使用情况

显示当前数据库的表空间使用情况

包括表空间名称、DBF 文件个数、总大小、剩余/占用大小等等，按照从到小排序

select a.tablespace_name as "表空间名称",
c.pieces as "DBF文件个数",
a.totalspace || 'M' as "总大小",
b.freespace || 'M' as "剩余大小",
a.totalspace - nvl(b.freespace, 0) || 'M' as "占用大小",
c.max_blocks as "最大块",
c.min_blocks as "最小块",
c.avg_blocks as "平均块",
c.sum_blocks as "块总数"
from (select t1.tablespace_name, sum(t1.bytes) / 1024 / 1024 as totalspace
from dba_data_files t1
group by t1.tablespace_name) a,
(select t2.tablespace_name, sum(t2.bytes) / 1024 / 1024 as freespace
from dba_free_space t2
group by t2.tablespace_name) b,
(select t.tablespace_name,
count(*) as pieces,
max(t.blocks) as max_blocks,
min(t.blocks) as min_blocks,
avg(t.blocks) as avg_blocks,
sum(t.blocks) as sum_blocks
from dba_free_space t
group by t.tablespace_name) c
where a.tablespace_name = b.tablespace_name
and b.tablespace_name = c.tablespace_name

显示如下，红圈内为默认创建的表空间