oracle所有表使用情况,oracle表及表空间使用情况

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

根据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_DATA36841.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_DATA22368.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%

………………