Oracle 查看錶空間使用率 SQL 指令碼

 

SQL 陳述式：

/* Formatted on 2012/5/31 14:51:13 (QP5 v5.185.11230.41888) */SELECT D.TABLESPACE_NAME,       SPACE || 'M' "SUM_SPACE(M)",       BLOCKS "SUM_BLOCKS",       SPACE - NVL (FREE_SPACE, 0) || 'M' "USED_SPACE(M)",       ROUND ( (1 - NVL (FREE_SPACE, 0) / SPACE) * 100, 2) || '%'          "USED_RATE(%)",       FREE_SPACE || 'M' "FREE_SPACE(M)"  FROM (  SELECT TABLESPACE_NAME,                 ROUND (SUM (BYTES) / (1024 * 1024), 2) SPACE,                 SUM (BLOCKS) BLOCKS            FROM DBA_DATA_FILES        GROUP BY TABLESPACE_NAME) D,       (  SELECT TABLESPACE_NAME,                 ROUND (SUM (BYTES) / (1024 * 1024), 2) FREE_SPACE            FROM DBA_FREE_SPACE        GROUP BY TABLESPACE_NAME) F WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+)UNION ALL                                                           --如果有暫存資料表空間SELECT D.TABLESPACE_NAME,       SPACE || 'M' "SUM_SPACE(M)",       BLOCKS SUM_BLOCKS,       USED_SPACE || 'M' "USED_SPACE(M)",       ROUND (NVL (USED_SPACE, 0) / SPACE * 100, 2) || '%' "USED_RATE(%)",       NVL (FREE_SPACE, 0) || 'M' "FREE_SPACE(M)"  FROM (  SELECT TABLESPACE_NAME,                 ROUND (SUM (BYTES) / (1024 * 1024), 2) SPACE,                 SUM (BLOCKS) BLOCKS            FROM DBA_TEMP_FILES        GROUP BY TABLESPACE_NAME) D,       (  SELECT TABLESPACE_NAME,                 ROUND (SUM (BYTES_USED) / (1024 * 1024), 2) USED_SPACE,                 ROUND (SUM (BYTES_FREE) / (1024 * 1024), 2) FREE_SPACE            FROM V$TEMP_SPACE_HEADER        GROUP BY TABLESPACE_NAME) F WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+)ORDER BY 1;

效果如下：

 

 

 

-------------------------------------------------------------------------------------------------------

著作權，文章允許轉載，但必須以連結方式註明源地址，否則追究法律責任!

Skype:            tianlesoftware

QQ:                 tianlesoftware@gmail.com

Email:             tianlesoftware@gmail.com

Blog:   http://www.tianlesoftware.com

Weibo:            http://weibo.com/tianlesoftware

Twitter: http://twitter.com/tianlesoftware

Facebook: http://www.facebook.com/tianlesoftware

Linkedin: http://cn.linkedin.com/in/tianlesoftware

 

 

-------加群需要在備忘說明Oracle資料表空間和資料檔案的關係，否則拒絕申請----

DBA1 群：62697716(滿);   DBA2 群：62697977(滿)  DBA3 群：62697850(滿)  

DBA 超級群：63306533(滿);  DBA4 群：83829929   DBA5群： 142216823

DBA6 群：158654907    DBA7 群：172855474   DBA總群：104207940