联系:手机/微信(+86 17813235971) QQ(107644445)
作者:惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]
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;
表空间大小估算
准确计算表空间大小