ITPub博客

首页 > 数据库 > Oracle > oracle表空间使用率查询

oracle表空间使用率查询

原创 Oracle 作者:zhcunique 时间:2021-03-04 13:39:35 0 删除 编辑

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 ;


来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/69994536/viewspace-2761013/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2021-02-04

  • 博文量
    63
  • 访问量
    460125