查看oracle表空間大小

查看所有表空間大小SQL>selecttablespace_name,sum(bytes)/1024/1024fromdba_data_files2groupbytablespace_name;已經(jīng)使用的表空間大小SQL>selecttablespace_name,sum(bytes)/1024/1024fromdba_free_space2groupbytablespace_name;所以使用空間可以這樣計算selecta.tablespace_name,total,free,total-freeusedfrom(selecttablespace_name,sum(bytes)/1024/1024totalfromdba_data_filesgroupbytablespace_name)a,(selecttablespace_name,sum(bytes)/1024/1024freefromdba_free_spacegroupbytablespace_name)bwherea.tablespace_name=b.tablespace_name;下面這條語句查看所有segment的大小。SelectSegment_Name,Sum(bytes)/1024/1024FromUser_ExtentsGroupBySegment_Name還有在命令行情況下如何將結(jié)果放到一個文件里。SQL>spoolout.txtSQL>select*fromv$database;SQL>spooloffSELECTUPPER(F.TABLESPACE_NAME)"表空間名",D.TOT_GROOTTE_MB"表空間大小(M)",D.TOT_GROOTTE_MB-F.TOTAL_BYTES'巳使用空間(M)",TO_CHAR(ROUND((D.TOT_GROOTTE_MB-F.TOTAL_BYTES)/D.TOT_GROOTTE_MB*100,2),'990.99')"使用比",F.TOTAL_BYTES'空閑空間(M)",F.MAX_BYTES"最大塊(M)"FROM(SELECTTABLESPACE_NAME,ROUND(SUM(BYTES)/(1024*1024),2)TOTAL_BYTES,ROUND(MAX(BYTES)/(1024*1024),2)MAX_BYTESFROMSYS.DBA_FREE_SPACEGROUPBYTABLESPACE_NAME)F,(SELECTDD.TABLESPACE_NAME,ROUND(SUM(DD.BYTES)/(1024*1024),2)TOT_GROOTTE_MBFROMSYS.DBA_DATA_FILESDDGROUPBYDD.TABLESPACE_NAME)DWHERED.TABLESPACE_NAME=F.TABLESPACE_NAMEORDERBY4DESC查詢表空間使用情況SELECTUPPER(F.TABLESPACE_NAME)"表空間名",D.TOT_GROOTTE_MB"表空間小(M)",D.TOT_GROOTTE_MB-FTOTAL_BYTES"已使用空間(M)",TO_CHAR(ROUND((D.TOT_GROOTTE_MB-F.TOTAL_BYTES)/D.TOT_GROOTTE_MB100,2),'990.99')||'%'"使用比",F.TOTAL_BYTES"空閑空間(M)",F.MAX_BYTES"最塊(M)"FROM(SELECTTABLESPACE_NAME,ROUND(SUM(BYTES)/(1024*1024),2)TOTAL_BYTES,ROUND(MAX(BYTES)/(1024*1024),2)MAX_BYTESFROMSYS.DBA_FREE_SPACEGROUPBYTABLESPACE_NAME)F,(SELECTDD.TABLESPACE_NAME,ROUND(SUM(DD.BYTES)/(1024*1024),2)TOT_GROOTTE_MBFROMSYS.DBA_DATA_FILESDDGROUPBYDD.TABLESPACE_NAME)DWHERED.TABLESPACE_NAME=F.TABLESPACE_NAMEORDERBY1--查詢表空間的freespaceselecttablespace_name,count(*)asextends,round(sum(bytes)/1024/1024,2)asMB,sum(blocks)asblocksfromdba_free_spacegroupbytablespace_name;--查詢表空間的總?cè)萘縮electtablespace_name,sum(bytes)/1024/1024asMBfromdba_data_filesgroupbytablespace_name;--查詢表空間使用率selecttotal.tablespace_name,round(total.MB,2)asTotal_MB,round(total.MB-free.MB,2)asUsed_MB,round((1-free.MB/total.MB)*100,2)||'%'asUsed_Pctfrom(selecttablespace_name,sum(bytes)/1024/1024asMBfromdba_free_spacegroupbytablespace_name)free,(selecttablespace_name,sum(bytes)/1024/1024asMBfromdba_data_filesgroupbytablespace_name)totalwherefree.tablespace_name=total.tablespace_name;SELECTUPPER(F.TABLESPACE_NAME)"表空間名",D.TOT_GROOTTE_MB"表空間大小(M)",D.TOT_GROOTTE_MB-F.TOTAL_BYTES"已使用空間(M)",TO_CHAR(ROUND((D.TOT_GROOTTE_MB-F.TOTAL_BYTES)/D.TOT_GROOTTE_MB*100,2),'990.99')"使用比",F.TOTAL_BYTES"空閑空間(M)",F.MAX_BYTES"最大塊(M)"FROM(SELECTTABLESPACE_NAME,ROUND(SUM(BYTES)/(1024*1024),2)TOTAL_BYTES,ROUND(MAX(BYTES)/(1024*1024),2)MAX_BYTESFROMSYS.DBA_FREE_SPACEGROUPBYTABLESPACE_NAME)F,(SELECTDD.TABLESPACE_NAME,RO