1、表空间大小
Select Tablespace_Name,Sum(bytes)/1024/1024 From Dba_Segments Group By Tablespace_Name order by Sum(bytes)/1024/1024 desc;
2、表占用空间
select segment_name, sum(bytes)/1024/1024 Mbytese from user_segments where segment_type='TABLE' group by segment_name order by segment_name; Select Segment_Name,Sum(bytes)/1024/1024 From User_Extents Group By Segment_Name order by Sum(bytes)/1024/1024 desc;
3、索引占用空间
select segment_name, sum(bytes)/1024/1024 Mbytese from user_segments where segment_type='INDEX' group by segment_name order by segment_name;
4、数据文件使用情况
select a.tablespace_name,
round(a.bytes / 1024 / 1024, 0)"总空间",
round((decode(b.bytes, null, 0, b.bytes)) / 1024 / 1024, 0)"使用空间",
round((decode(b.bytes, null, 0, b.bytes)) / a.bytes * 100, 1)"使用率",
c.file_name,
c.status
from sys.sm$ts_avail a, sys.sm$ts_free b, dba_data_files c
where a.tablespace_name = b.tablespace_name(+)
and a.tablespace_name = c.tablespace_name
order by a.tablespace_name;
-----------------------------------------------------------------------
select b.file_id 文件ID,
b.tablespace_name 表空间,
b.file_name 物理文件名,
b.bytes / 1024 / 1024 大小M,
c.max_extents / 1024 / 1024 可扩展数M,
b.bytes / 1024 / 1024 + c.max_extents / 1024 / 1024 总大小M,
trunc((b.bytes - sum(nvl(a.bytes, 0))) / 1024 / 1024) 已使用M,
trunc(sum(nvl(a.bytes, 0)) / 1024 / 1024) 剩余M,
trunc(sum(nvl(a.bytes, 0)) / (b.bytes) * 100, 2) 剩余比
from dba_free_space a, dba_data_files b, dba_tablespaces c
where a.file_id = b.file_id
and b.tablespace_name = c.tablespace_name
group by b.tablespace_name,
b.file_name,
b.file_id,
b.bytes,
c.max_extents,
b.bytes / 1024 / 1024 + c.max_extents / 1024 / 1024
order by b.file_id;
5、表空间使用统计
select a.tablespace_name,
a.bytes / 1024 / 1024 "Sum MB",
(a.bytes - b.bytes) / 1024 / 1024 "used MB",
b.bytes / 1024 / 1024 "free MB",
round(((a.bytes - b.bytes) / a.bytes) * 100, 2) "percent_used"
from (select tablespace_name, sum(bytes) bytes
from dba_data_files
group by tablespace_name) a,
(select tablespace_name, sum(bytes) bytes, max(bytes) largest
from dba_free_space
group by tablespace_name) b
where a.tablespace_name = b.tablespace_name
order by ((a.bytes - b.bytes) / a.bytes) desc;
