《2022年查看oracle表空间大小 .pdf》由会员分享，可在线阅读，更多相关《2022年查看oracle表空间大小 .pdf（3页珍藏版）》请在taowenge.com淘文阁网|工程机械CAD图纸|机械工程制图|CAD装配图下载|SolidWorks_CaTia_CAD_UG_PROE_设计图分享下载上搜索。
1、1. 查看所有表空间大小SQL select tablespace_name,sum(bytes)/1024/1024 from dba_data_files 2 group by tablespace_name; 2. 已经使用的表空间大小SQL select tablespace_name,sum(bytes)/1024/1024 from dba_free_space 2 group by tablespace_name; 3. 所以使用空间可以这样计算select a.tablespace_name,total,free,total-free used from ( select ta
2、blespace_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; 4. 下面这条语句查看所有segment 的大小。Select Segment_Name,Sum(bytes)/1024/1024 Fr
3、om User_Extents Group By Segment_Name 5. 还有在命令行情况下如何将结果放到一个文件里。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_
4、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 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - -
5、 第 1 页，共 3 页 - - - - - - - - - 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_N
6、AME) 表空间名 , 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,
7、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 - 查询
8、表空间的free space select tablespace_name, count(*) as extends, round(sum(bytes) / 1024 / 1024, 2) as MB, sum(blocks) as blocks from dba_free_space group by tablespace_name; - 查询表空间的总容量select tablespace_name, sum(bytes) / 1024 / 1024 as MB from dba_data_files group by tablespace_name; - 查询表空间使用率select t
9、otal.tablespace_name, 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 2 页，共 3 页 - - - - - - - - - round(total.MB, 2) as Total_MB, round(total.MB - free.MB, 2) as Used_MB, round(1 - free.MB / total.MB) * 100, 2) | % as Used_Pct from (select tablespace_name, sum(bytes)
10、/ 1024 / 1024 as MB from dba_free_space group by tablespace_name) free, (select tablespace_name, sum(bytes) / 1024 / 1024 as MB from dba_data_files group by tablespace_name) total where free.tablespace_name = total.tablespace_name; SELECT UPPER(F.TABLESPACE_NAME) 表空间名 , D.TOT_GROOTTE_MB 表空间大小 (M), D
11、.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)
12、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 BYDD.TABLESPACE_NAME) D WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME ORDER BY 4 DESC名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 3 页，共 3 页 - - - - - - - - -