查看oracle表空间大小.docxVIP

查看所有表空间大小 SQL select tablespace_name,sum(bytes)/1024/1024 from dba_data_files 2 group by tablespace_name; 已经使用的表空间大小 SQL select tablespace_name,sum(bytes)/1024/1024 from dba_free_space 2 group by tablespace_name; 所以使用空间可以这样计算 select a.tablespace_name,total,free,total-free used from ( select tablespace_name,sum(bytes)/1024/1024 total from dba_data_files group by tablespace_name) a, ( select tablespace_name,sum(bytes)/1024/1024 free from dba_free_space group by tablespace_name) b where a.tablespace_name=b.tablespace_name; 下面这条语句查看所有segment 的大小。 Select Segment_Name,Sum(bytes)/1024/1024 From User_Extents Group By Segment_Name 还有在命令行情况下如何将结果放到一个文件里。SQL spool out.txt SQL select * from v$database; SQL spool off SELECT UPPER(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 (SELECT TABLESPACE_NAME, ROUND(SUM(BYTES) / (1024 * 1024), 2) TOTAL_BYTES, ROUND(MAX(BYTES) / (1024 * 1024), 2) MAX_BYTES FROM SYS.DBA_FREE_SPACE GROUP BY TABLESPACE_NAME) F, (SELECT DD.TABLESPACE_NAME, ROUND(SUM(DD.BYTES) / (1024 * 1024), 2) TOT_GROOTTE_MB FROM SYS.DBA_DATA_FILES DD GROUP BY DD.TABLESPACE_NAME) D WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME ORDER BY 4 DESC --查询表空间使用情况 SELECT UPPER(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 (SELECT TABLESPACE_NAME, ROUND(SUM(BYTES) / (1024 * 1024), 2) TOTAL_BYTES, ROUND(MAX(BYTES) / (1024 * 1024), 2) MAX_BYTES FROM SYS.DBA_FREE_SPACE GROUP BY TABLESPACE_NAME) F , (SELECT DD.TABLESPACE_NAME, ROUND(SUM(DD.BYTES) / (1024 * 1024), 2) TOT_GROOTTE_MB FROM SYS.DBA_DATA_FILES DD GROUP BY DD.TABLESPACE_NAME) D WHERE D.TABLESPACE_NAME = F .TABLESPACE_NAME ORDER BY 1 --查询表空间的 free spac