貼一個求表空間的sql 語句

lnwxzyp發表於2011-05-13
SQL
    這段時間經常發現用以前的sql查出來的表空間情況不夠準確，先是表空間佔用達到100%的用以前的SQL語言查不出來，後來發現表空間狀態為offline的也查不出來，也是自己在原來的語句的基礎上寫了一個，貌似效率不夠高，好在每天也只是跑一次而已，貼出來供大家參考。

set linesize 300
set pagesize 999
col TABLESPACE_NAME format a30
col SPACE_STATUS format a10
col "SUM_SPACE(M)" format 999999999.9
col "USED_SPACE(M)" format 999999999.9
col SPACE_TYPE format a12
col "USED_RATE(%)" format a12
SELECT  Z.*,V.STATUS SPACE_STATUS FROM (SELECT D.TABLESPACE_NAME,SPACE "SUM_SPACE(M)",SPACE-NVL(FREE_SPACE,0) "USED_SPACE(M)", TO_CHAR(ROUND((1-NVL(FREE_SPACE,0)/SPACE)*100,2),'990.99')||'%' "USED_RATE(%)", 'Permanent' SPACE_TYPE
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(+)) Z,(SELECT A.TABLESPACE_NAME,MAX(B.STATUS) STATUS FROM DBA_DATA_FILES A,V$DATAFILE B WHERE A.FILE_ID = B.FILE# GROUP BY TABLESPACE_NAME) V 
WHERE Z.TABLESPACE_NAME = V.TABLESPACE_NAME(+)
UNION ALL  
SELECT Z.*,V.STATUS SPACE_STATUS FROM (SELECT D.TABLESPACE_NAME,SPACE "SUM_SPACE(M)",USED_SPACE "USED_SPACE(M)",TO_CHAR(ROUND(NVL(USED_SPACE,0)/SPACE*100,2),'990.99')||'%' "USED_RATE(%)" ,'Temporary' SPACE_TYPE
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(+)) Z,(SELECT A.TABLESPACE_NAME,MAX(B.STATUS) STATUS FROM DBA_DATA_FILES A,V$TEMPFILE B WHERE A.FILE_ID = B.FILE# GROUP BY TABLESPACE_NAME) V 
WHERE Z.TABLESPACE_NAME = V.TABLESPACE_NAME(+);

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/12216142/viewspace-695245/，如需轉載，請註明出處，否則將追究法律責任。

相關文章