oracle 3

查询表空间大小：
SELECT A.TABLESPACE_NAME,
       A.FILENUMBER,
       ROUND(A.TBSCURRENTSIZEINMB/1024, 2) TOTAL_SIZE_G,
       ROUND(B.SEGMENTSIZEINMB/1024, 2) USED_SIZE_G,
       ROUND((A.TBSCURRENTSIZEINMB-B.SEGMENTSIZEINMB)/1024, 2) FREE_SIZE_G,
       ROUND(NVL(B.SEGMENTSIZEINMB, 0) / A.TBSCURRENTSIZEINMB, 4) * 100 CAPACITYUSED
  FROM (SELECT TABLESPACE_NAME,
               COUNT(1) FILENUMBER,
               SUM(BYTES) / 1024 / 1024 TBSCURRENTSIZEINMB
          FROM DBA_DATA_FILES
         GROUP BY TABLESPACE_NAME) A,
       (SELECT TABLESPACE_NAME, SUM(BYTES) / 1024 / 1024 SEGMENTSIZEINMB
          FROM DBA_SEGMENTS
         GROUP BY TABLESPACE_NAME) B
 WHERE A.TABLESPACE_NAME = B.TABLESPACE_NAME
    --AND (A.TABLESPACE_NAME LIKE '%ODSREP%' OR  A.TABLESPACE_NAME LIKE '%DMREP%' OR A.TABLESPACE_NAME LIKE '%BLREP%')
   --AND B.SEGMENTSIZEINMB / A.TBSCURRENTSIZEINMB < 0.5
   --AND A.TBSCURRENTSIZEINMB > 10 * 1024
   --AND a.tablespace_name LIKE 'BLAP%'
  --AND A.TABLESPACE_NAME NOT LIKE '%UNDO%'
 ORDER BY CAPACITYUSED
;




SELECT   a.tablespace_name, 
         ROUND (a.max_size) "WILL_TOTAL(M)",
         nvl((ROUND (a.max_size)- ROUND (a.total_size)+ ROUND (b.free_size)),0) "WILL_FREE(M)",
         nvl((ROUND ((a.total_size - b.free_size) / a.max_size * 100,1)),100) "WILL_USAGE(%)"
    FROM (SELECT   aa.tablespace_name,
                   SUM (aa.max_size) / 1024 / 1024 max_size,
                   SUM (aa.BYTES) / 1024 / 1024 total_size
              FROM (SELECT tablespace_name,BYTES max_size,BYTES
                      FROM dba_data_files) aa
          GROUP BY tablespace_name) a,
         (SELECT   tablespace_name, SUM (BYTES / 1024 / 1024) free_size
              FROM dba_free_space
          GROUP BY tablespace_name) b
   WHERE a.tablespace_name = b.tablespace_name(+)
     AND a.tablespace_name NOT LIKE 'UNDOTBS%'

ORDER BY "WILL_USAGE(%)" asc;