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_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"，共同學習，共同成長！！！