查看表空间使用的两条常用SQL

2014-12-8 11:13 906 0

摘要: 1. 查看表空间大小：SELECT tablespace_name, SUM(bytes)/1024/1024 total FROM DBA_FREE_SPACE GROUP BY ...

关键词： nbsp tablespace bytes 1024 FREE SUM file used HEADING FORMAT

1. 查看表空间大小：SELECT tablespace_name, SUM(bytes)/1024/1024 total FROM DBA_FREE_SPACE GROUP BY tablespace_name ORDER BY 2 DESC;SQL> SELECT tablespace_name, SUM(bytes)/1024/1024 || 'MB' total FROM DBA_FREE_SPACE GROUP BY tablespace_name ORDER BY 2 DESC;注意下上面两条SQL的排序，显然第一条SQL是我们需要的结果，按照表空间大小降序排列。之所以第二条SQL的排序乱，是因为使用了|| 'MB'连接字符串，则这个字段就作为字符串类型检索，排序时就会按照字符的ASCII进行排序。2. 查看表空间使用率：SQL> BREAK ON REPORTSQL> COMPUT SUM OF tbsp_size ON REPORTSQL> compute SUM OF used ON REPORTSQL> compute SUM OF free ON REPORTSQL> COL tbspname FORMAT a20 HEADING 'Tablespace Name'SQL> COL tbsp_size FORMAT 999,999 HEADING 'Size|(MB)'SQL> COL used FORMAT 999,999 HEADING 'Used|(MB)'SQL> COL free FORMAT 999,999 HEADING 'Free|(MB)'SQL> COL pct_used FORMAT 999,999 HEADING '% Used'SQL> SELECT df.tablespace_name tbspname,sum(df.bytes)/1024/1024 tbsp_size,nvl(sum(e.used_bytes)/1024/1024,0) used,nvl(sum(f.free_bytes)/1024/1024,0) free,nvl((sum(e.used_bytes)*100)/sum(df.bytes),0) pct_usedFROM DBA_DATA_FILES df, (SELECT file_id, SUM(nvl(bytes, 0)) used_bytesFROM dba_extentsGROUP BY file_id) e,(SELECT MAX(bytes) free_bytes, file_idFROM dba_free_spaceGROUP BY file_id) fWHERE e.file_id(+) = df.file_idAND df.file_id = f.file_id(+)GROUP BY df.tablespace_nameORDER BY 5 DESC;视图定义：

声明：文章版权归原作者所有部分文章转自互联网如有侵权请联系 [邮箱地址] 删除