查看oracle表空间大小

airmancn 贡献于2013-01-20

作者 MaoFulin  创建于2010-06-01 01:31:00   修改者MaoFulin  修改于2010-06-01 01:35:00字数3009

文档摘要:查看oracle表空间大小
关键词:

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 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; 4. 下面这条语句查看所有segment的大小。 Select Segment_Name,Sum(bytes)/1024/1024 From 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_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 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 total.tablespace_name,   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) / 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.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

下载文档到电脑,查找使用更方便

文档的实际排版效果,会与网站的显示效果略有不同!!

需要 3 金币 [ 分享文档获得金币 ] 1 人已下载

下载文档