指引网

当前位置: 主页 > 数据库 > SQLServer >

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

来源:网络 作者:佚名 点击: 时间:2018-03-14 17:51
[摘要] 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) 102

 

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 REPORT
SQL> COMPUT SUM OF tbsp_size ON REPORT
SQL> compute SUM OF used        ON REPORT
SQL> compute SUM OF free         ON REPORT

SQL> 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_used
FROM DBA_DATA_FILES df, 
(SELECT file_id, SUM(nvl(bytes, 0)) used_bytes
FROM dba_extents
GROUP BY file_id) e,
(SELECT MAX(bytes) free_bytes, file_id
FROM dba_free_space
GROUP BY file_id) f
WHERE e.file_id(+) = df.file_id
AND df.file_id = f.file_id(+)
GROUP BY df.tablespace_name
ORDER BY 5 DESC;

\


视图定义:
\

\

 

------分隔线----------------------------