一 查看表或索引占用空间大小

根据oracle体系结构(表,段,区,块),一张表就是一个或多个段;
其中表增长的最小单位是去extent
查看用户表、索引、分区表占用空间

select segment_name, sum(bytes)/1024/1024||'M' Mbytese from user_segments group by segment_name order by 2 desc;

表占用空间

select segment_name, sum(bytes)/1024/1024||'M' Mbytese from user_segments where segment_type='TABLE' group by segment_name order by 2 desc;

索引占用空间

select segment_name ,sum(bytes)/1024/1024||'M' from user_segments where segment_type ='INDEX' group by segment_name order by 2 desc;

分区表 TABLE PARTITION 占用空间

select segment_name,sum(bytes)/1024/1024||'M' Mbytes from user_segments where segment_type='TABLE PARTITION' group by segment_name order by 2 desc;

二 表空间大小使用情况

查看数据文件大小

select tablespace_name,file_name,bytes/1024/1024||'M' ssize from dba_data_files order by 1;

查看表空间大小

select tablespace_name,sum(bytes)/1024/1024||'M' msize from dba_data_files group by tablespace_name order by 1;
TABLESPACE_NAME MSIZE

1 APP_DATA 36841.38671875M
……
查看表空间剩余大小

select tablespace_name,sum(bytes)/1024/1024||'M' fsize from dba_free_space group by tablespace_name order by 1;
TABLESPACE_NAME FSIZE
**1 APP_DATA** **22368.32421875M**

终上所述:
表空间APP_DATA大小 36841.38671875M ,剩余大小22368.32421875M ,那么使用大小= 36841.38671875M-22368.32421875M = 14473.0625M
APP_DATA 剩余百分比
select round((22368.32421875/36841.38671875)*100,2)||'%' from dual;---60.72%
APP_DATA 使用百分比
select round((1-(22368.32421875/36841.38671875))*100,2)||'%' from dual; ---39.28%

终上所述:
查看空间使用百分比

select a.tablespace_name,a.msize total,b.fsize free,a.msize-b.fsize usein,round(((a.msize-b.fsize)/a.msize)*100,2)||'%' "useid%",round((b.fsize/a.msize)*100,2)||'%' "free%" from
(select tablespace_name,sum(bytes)/1024/1024 msize from dba_data_files group by tablespace_name) a,
(select tablespace_name,sum(bytes)/1024/1024 fsize from dba_free_space group by tablespace_name) b
where a.tablespace_name=b.tablespace_name order by 1;

TABLESPACE_NAME TOTAL FREE USEIN useid% free%
1 APP_DATA 36841.38671875 22368.32421875 14473.0625 39.28% 60.72%

最后修改:2022 年 03 月 01 日
如果觉得我的文章对你有用,请随意赞赏