1、要知道表空间大小，就要明白两种增加表空间大小的方式第一种：格式化数据文件初始大小并设置自增长到最大值create tablespace d_test1 datafile '/test1_data/datafile01.dbf' size 10m autoextend on next 5m maxsize 100m;
2、第二种：格式化数据文件初始大小不设置自增长，当然也就没有最大值alter tablespace d_test1 add datafile '/test1_data/datafile02.dbf' size 10m;
3、不管是第一种还是第二种方式数据的物理文件大小都是dba_data_files表bytes列的值，而bytes列可能是已经被用满了，或者还没有用满还有剩余，但是不管怎么样，我们可以根据bytes列和maxbytes获取表空间总大小。
4、分别统计增加表空见两种方式的数据文件大小总值SELECT tablespace_name,sum(MAXBYTES) TOTAL FROM DBA_DATA_FILES TWHERE T.TABLESPACE_NAME NOT IN ('USERS','SYSAUX','UNDOTBS1','SYSTEM','UNDOTBS2')AND T.AUTOEXTENSIBLE='YES' group by tablespace_nameUNION ALLSELECT tablespace_name,sum(bytes) TOTAL FROM DBA_DATA_FILES TWHERE T.TABLESPACE_NAME NOT IN ('USERS','SYSAUX','UNDOTBS1','SYSTEM','UNDOTBS2')AND T.AUTOEXTENSIBLE='NO' group by tablespace_name
5、统计增加表空见两种方式的数据文件大小总值，也就是统计表空间总大小掳姗浚瑙WITH TABLESPACE_TOTAL AS(SELECT tablespace_name,sum(MAXB鳔柩寞泷YTES) TOTAL FROM DBA_DATA_FILES T WHERE T.TABLESPACE_NAME NOT IN ('USERS','SYSAUX','UNDOTBS1','SYSTEM','UNDOTBS2')AND T.AUTOEXTENSIBLE='YES' group by tablespace_nameUNION ALLSELECT tablespace_name,sum(bytes) TOTAL FROM DBA_DATA_FILES T WHERE T.TABLESPACE_NAME NOT IN ('USERS','SYSAUX','UNDOTBS1','SYSTEM','UNDOTBS2')AND T.AUTOEXTENSIBLE='NO' group by tablespace_name)SELECT TABLESPACE_NAME,SUM(TOTAL) TOTAL FROM TABLESPACE_TOTAL GROUP BY TABLESPACE_NAME
6、那么表空间剩余大小如何统计呢?首先看看表空间已使用的大小select TABLESPACE_NAME,sum(bytes) from dba_segments where tablespace_name NOT IN ('USERS','SYSAUX','UNDOTBS1','SYSTEM','UNDOTBS2')GROUP BY TABLESPACE_NAME
7、根据dba_data_files和dba_segments统计表空间总大小和剩余大小SELECT A.TABLESPACE_NAME,B.TOTAL/1024/1024匀舶热圾||'M',(B.TOTAL-A.USE)/1024/1024||'M' FREE FROM(select TABLESPACE_NAME,sum(bytes) as USE from dba_segments where tablespace_name NOT IN ('USERS','SYSAUX','UNDOTBS1','SYSTEM','UNDOTBS2')GROUP BY TABLESPACE_NAME) A ,(WITH TABLESPACE_TOTAL AS(SELECT tablespace_name,sum(MAXBYTES) TOTAL FROM DBA_DATA_FILES TWHERE T.TABLESPACE_NAME NOT IN ('USERS','SYSAUX','UNDOTBS1','SYSTEM','UNDOTBS2')AND T.AUTOEXTENSIBLE='YES' group by tablespace_nameUNION ALLSELECT tablespace_name,sum(bytes) TOTAL FROM DBA_DATA_FILES T WHERE T.TABLESPACE_NAME NOT IN ('USERS','SYSAUX','UNDOTBS1','SYSTEM','UNDOTBS2')AND T.AUTOEXTENSIBLE='NO' group by tablespace_name) SELECT TABLESPACE_NAME,SUM(TOTAL) TOTAL FROM TABLESPACE_TOTAL GROUP BY TABLESPACE_NAME) BWHERE A.TABLESPACE_NAME=B.TABLESPACE_NAME
8、注意：dba_free_space统计信息select tablespace_name,FILE_ID,bytes/1024/1024 from dba_free_spacewhere tablespace_name NOT IN ('USERS','SYSAUX','UNDOTBS1','SYSTEM','UNDOTBS2')可以看出dba_free_space表是dba_data_files表的各个数据文件bytes列已经真正使用后剩余大小
9、根据dba_data_files和dba_free_space统计表空间大小和表空间港错恃瘦剩余大小SELECT A.TABLESPACE_NAME,B.T晦倘佳鳎OTAL/1024/1024||'M',(B.TOTAL-A.USE)/1024/1024||'M' FREE FROM(select c.tablespace_name,(c.p_use-d.p_free) as use from(select tablespace_name,sum(bytes) as p_use from dba_data_fileswhere tablespace_name NOT IN ('USERS','SYSAUX','UNDOTBS1','SYSTEM','UNDOTBS2')group by tablespace_name) C,(select tablespace_name,sum(bytes) as p_free from dba_free_spacewhere tablespace_name NOT IN ('USERS','SYSAUX','UNDOTBS1','SYSTEM','UNDOTBS2')group by tablespace_name) D where C.tablespace_name=D.tablespace_name) A ,(WITH TABLESPACE_TOTAL AS(SELECT tablespace_name,sum(MAXBYTES) TOTAL FROM DBA_DATA_FILES TWHERE T.TABLESPACE_NAME NOT IN ('USERS','SYSAUX','UNDOTBS1','SYSTEM','UNDOTBS2')AND T.AUTOEXTENSIBLE='YES' group by tablespace_nameUNION ALLSELECT tablespace_name,sum(bytes) TOTAL FROM DBA_DATA_FILES T WHERE T.TABLESPACE_NAME NOT IN ('USERS','SYSAUX','UNDOTBS1','SYSTEM','UNDOTBS2')AND T.AUTOEXTENSIBLE='NO' group by tablespace_name) SELECT TABLESPACE_NAME,SUM(TOTAL) TOTAL FROM TABLESPACE_TOTAL GROUP BY TABLESPACE_NAME) BWHERE A.TABLESPACE_NAME=B.TABLESPACE_NAME
10、不管是根据dba_data_files和dba_free_space统计表空间大小和表空间剩余大小还是根据dba_data_files和dba_segments统计表空间大小和表空间剩余大小,两种方法的偏差值都不大，当然我还是建议采用根据dba_data_files和dba_free_space统计表空间大小和表空间剩余大
