Oracle 数据库表、大字段、表空间大小统计

1、查找特定表大小

select round(BYTES/1024/1024,2)||'M' from Dba_Segments where segment_name=upper('表名')；

2、查找数据库中占用空间较大的对象

SELECT OWNER,SEGMENT_NAME,SUM(bytes/1024/1024/1024 ) sum from Dba_Segments group by owner,segment_name having sum(bytes/1024/1024/1024 )>2 order by 3 desc; 

3、统计Oracle数据库用户所有表的大小

 SELECT OWNER as "用户名", sum(BYTES) / 1024 / 1024 / 1024 as "所有表的大小(GB)"
  FROM DBA_SEGMENTS
 WHERE SEGMENT_NAME in (select t2.OBJECT_NAME
                          from dba_objects t2
                         where t2.OBJECT_TYPE = 'TABLE')
 group by OWNER order by 2 desc;

4、统计大字段占用空间

SELECT B.TABLE_NAME,
B.COLUMN_NAME,
A.SEGMENT_NAME,
a.SEGMENT_TYPE,
ROUND(SUM(A.BYTES / 1024 / 1024 / 1024), 2) G
FROM DBA_SEGMENTS A
LEFT JOIN DBA_LOBS B
ON A.OWNER = B.OWNER
AND A.SEGMENT_NAME = B.SEGMENT_NAME

--WHERE B.SEGMENT_NAME = 'SYS_LOBxxx$$'

HAVING ROUND(SUM(A.BYTES / 1024 / 1024 / 1024), 2) >1
GROUP BY B.TABLE_NAME, B.COLUMN_NAME, A.SEGMENT_NAME,a.SEGMENT_TYPE order by 5 desc;

 

4、查看每个表空间的大小

 Select Tablespace_Name,Sum(bytes)/1024/1024 From Dba_Segments Group By Tablespace_Name order by 2 desc;

 

5、数据文件使用情况

select 　　b.file_name 物理文件名,
       　　b.tablespace_name 表空间,
       　　b.bytes / 1024 / 1024 大小M,
       　　(b.bytes - sum(nvl(a.bytes, 0))) / 1024 / 1024 已使用M,
       　　substr((b.bytes - sum(nvl(a.bytes, 0))) / (b.bytes) * 100, 1, 5) 利用率 　　from dba_free_space a,
       dba_data_files b 　　where a.file_id = b.file_id 　　group by b.tablespace_name,
       b.file_name,
       b.bytes 　　order by b.tablespace_name;