查看oracle表空间大小

查看所有表空间大小SQL>selecttablespace_name,sum(bytes)/1024/1024fromdba_data_files2groupbytablespace_name;已经使用的表空间大小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还有在命令行情况下如何将结果放到一个文件里。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;--查询表空间的总容量selecttablespace_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