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

根据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%

………………

欢迎关注我的微信公众号"IT小Chen"，共同学习，共同成长！！！