oracle查看具体表占用空间

1、查看表空间对应日志文件selecttablespace_name, file_id, file_name,round(bytes/(1024*1024),0) total_spacefrom dba_data_filesorder by tablespace_name;2、查看表空间总大小、使用率、剩余空间selecta.tablespace_name, total, free, total-

毕九生

16066人浏览 · 2022-04-07 14:16:48

毕九生 · 2022-04-07 14:16:48 发布

1、查看表空间对应日志文件

select
  tablespace_name, file_id, file_name,
  round(bytes/(1024*1024),0) total_space
from dba_data_files
order by tablespace_name
;

2、查看表空间总大小、使用率、剩余空间

select
  a.tablespace_name, total, free, total-free as used, substr(free/total * 100, 1, 5) as "FREE%", substr((total - free)/total * 100, 1, 5) as "USED%"
from
  (select tablespace_name, sum(bytes)/1024/1024 as total from dba_data_files group by tablespace_name) a,
  (select tablespace_name, sum(bytes)/1024/1024 as free from dba_free_space group by tablespace_name) b
where a.tablespace_name = b.tablespace_name
order by a.tablespace_name
;

3、查看具体表的占用空间大小

select * from (
  select t.tablespace_name,t.owner, t.segment_name, t.segment_type, sum(t.bytes / 1024 / 1024) mb
  from dba_segments t
  where t.segment_type='TABLE'
  group by t.tablespace_name,t.OWNER, t.segment_name, t.segment_type
) t
order by t.mb desc
;