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;
>oracle数据库怎么查询表

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。