oracle表空間使用率查詢

use of max

SELECT A. TABLESPACE_NAME ,             

      ROUND((A. BYTES_ALLOC - NVL( B . BYTES_FREE , 0 )) / 1024 / 1024 ) MEGS_USED ,

      ROUND(A. MAXBYTES / 1048576 ) MAX,

      ROUND((A. BYTES_ALLOC - NVL( B . BYTES_FREE , 0 )) * 100 /A. MAXBYTES ) used_of_max 

FROM (SELECT F . TABLESPACE_NAME ,

            SUM( F . BYTES ) BYTES_ALLOC ,

            SUM(DECODE( F . AUTOEXTENSIBLE , 'YES' , F . MAXBYTES , 'NO' , F . BYTES )) MAXBYTES

        FROM DBA_DATA_FILES F

      GROUP BY TABLESPACE_NAME ) A,

              (SELECT F . TABLESPACE_NAME , SUM( F . BYTES ) BYTES_FREE

                  FROM DBA_FREE_SPACE F

                GROUP BY TABLESPACE_NAME ) B

        WHERE A. TABLESPACE_NAME = B . TABLESPACE_NAME (+)

use of allocation

SELECT total . tablespace_name ,

       Round( total . MB , 2 )            AS Total_MB ,

       Round( total . MB - nvl( free . MB , 0 ), 2 ) AS Used_MB ,

       Round(nvl( free . MB , 0 ), 2 ) AS Free_MB ,

       Round(( 1 - nvl( free . MB , 0 ) / total . MB ) * 100 , 2 )   || '%' AS Used_Pct ,

       Round((nvl( free . MB , 0 ) / total . MB ) * 100 , 2 )   || '%'       AS Free_Pct

FROM    (SELECT tablespace_name ,

               Sum( bytes ) / 1024 / 1024 AS MB

        FROM    dba_free_space

        GROUP   BY tablespace_name ) free

        full join

  ( SELECT tablespace_name ,

               Sum( bytes ) / 1024 / 1024 AS MB

        FROM    dba_data_files

        GROUP   BY tablespace_name ) total

    on   free . tablespace_name = total . tablespace_name ;