检测数据库大小的sql

DBA的日常功能SQL之一，绝对原创。

检测数据库大小的SQL，一个SQL搞定。

set lines 500
set pages 500


col gather_level format a20
col database_name format a8
col tablespace_name format a20
col datafile_name format a40
col autoextensible format a10
col maxsum_gb format 9990.000
col sum_gb format 9990.999
col free_gb format 9990.999
col used_gb format 9990.999
col used_precent format 9990.999
col used_of_max_precent format 9990.999


with tmp_datafile as  (
      select *
      from (
            --表空间的数据文件的使用率
            select 'DATAFILE' as gather_level,
                   (select dbid from v$database) AS DATABASE_ID,
                   (select name from v$database) AS DATABASE_NAME,
                   dd.tablespace_name,
                   dd.tablespace_name || '-' || dd.file_name as datafile_name,
                   dd.AUTOEXTENSIBLE,
                   nvl(round(sum(case when dd.maxbytes=0 then dd.BYTES else dd.MAXBYTES end) / 1024 / 1024 / 1024, 3),0) maxsum_GB,
                   nvl(round(sum(dd.bytes) / 1024 / 1024 / 1024, 3),0) sum_GB,
                   nvl(round(sum(nvl(dt.bytes, 0)) / 1024 / 1024 / 1024, 3),0) free_GB,
                   nvl(round(( sum(dd.bytes) - sum(nvl(dt.bytes, 0)) ) / 1024 / 1024 / 1024,  3),0) as used_GB,
                   nvl(round(( sum(dd.bytes) - sum(nvl(dt.bytes, 0)) ) / sum(dd.bytes), 6) * 100,0) used_precent,
                   nvl(round(( sum(dd.bytes) - sum(nvl(dt.bytes, 0)) ) / sum(case when dd.maxbytes=0 then dd.BYTES else dd.MAXBYTES end), 6) * 100,0) used_of_max_precent
              from ( select /*+ gather_plan_statistics */
                     tablespace_name, file_id, sum(bytes) bytes
                      from dba_free_space
                     group by tablespace_name, file_id ) dt,
                   dba_data_files dd
             where 1 = 1
               and dt.file_id(+) = dd.file_id
             group by dd.tablespace_name,dd.tablespace_name || '-' || dd.file_name,dd.AUTOEXTENSIBLE
             order by dd.tablespace_name,dd.tablespace_name || '-' || dd.file_name,dd.AUTOEXTENSIBLE
           )
      union all
      select *
      from (
           --临时表空间的数据文件的使用率
           select/*+ no_merge(dt)*/ 
                 'TEMPDATAFILE' as gather_level,
                 (select dbid from v$database) AS DATABASE_ID,
                 (select name from v$database) AS DATABASE_NAME,
                 dtf.tablespace_name,
                 dtf.tablespace_name || '-' || dtf.file_name as datafile_name,
                 dtf.autoextensible,
                 nvl(round(sum(case when dtf.maxbytes=0 then dtf.BYTES else dtf.MAXBYTES end) / 1024 / 1024 / 1024, 3),0) maxsum_GB,
                 nvl(round(sum(dtf.bytes) / 1024 / 1024 / 1024, 3),0) sum_GB,
                 nvl(round((sum(nvl(dtf.bytes,0)) - sum(nvl(dt.temp_used_size, 0))) / 1024 / 1024 / 1024, 3),0) as free_GB,
                 nvl(round(sum(dt.temp_used_size) / 1024 / 1024 / 1024, 3),0) as used_GB,
                 nvl(round(sum(dt.temp_used_size) / sum(dtf.bytes), 6) * 100,0) used_precent,
                 nvl(round(sum(dt.temp_used_size) / sum(case when dtf.maxbytes=0 then dtf.BYTES else dtf.MAXBYTES end), 6) * 100,0) used_of_max_precent
           from ( select/*+ gather_plan_statistics */ 
                        tu.TABLESPACE,tu.SEGRFNO#,
                        sum(tu.BLOCKS*(select vp.VALUE from v$parameter vp where 1=1 and vp.NAME='db_block_size')) as temp_used_size
                 from v$tempseg_usage tu
                  group by tu.TABLESPACE, tu.SEGRFNO#
                  ) dt,
                 dba_temp_files dtf
            where 1=1
             and dt.tablespace(+)=dtf.TABLESPACE_NAME
             and dt.SEGRFNO#(+) = dtf.RELATIVE_FNO     
           group by dtf.tablespace_name,dtf.tablespace_name || '-' || dtf.file_name,dtf.AUTOEXTENSIBLE
           order by dtf.tablespace_name,dtf.tablespace_name || '-' || dtf.file_name,dtf.AUTOEXTENSIBLE
         ) 
 )
 --数据库的信息    
 select *
 from (
        select  
              --grouping(tp0.database_name)||grouping(tp0.gather_level),
              decode(grouping(tp0.gather_level),0,decode(tp0.gather_level,'DATAFILE','DATABASE NO TEMP','DATABASE ONLY TEMP'),'DATABASE WITH TEMP' ) AS GATHER_LEVEL,
              tp0.database_id,              
              tp0.database_name,
              '' as tablespace_name,
              '' as datafile_name,
              '' as AUTOEXTENSIBLE,
              sum(tp0.maxsum_gb) as maxsum_gb,
              sum(tp0.sum_gb) as sum_gb,
              sum(tp0.free_gb) as free_gb,
              sum(tp0.used_gb) as used_gb,
              round(sum(tp0.used_gb) / sum(tp0.sum_gb), 6) * 100  as used_precent,
              round(sum(tp0.used_gb) / sum(tp0.maxsum_gb), 6) * 100  as used_of_max_precent
        from tmp_datafile tp0
        where 1=1
        group by rollup((tp0.database_id,tp0.database_name),tp0.gather_level)
        having not grouping(tp0.database_name)||grouping(tp0.gather_level)=11
        order by tp0.gather_level
      ) t_database
 union all
 --表空间的信息
 select *
 from (
        select 
              decode(tp1.gather_level,'DATAFILE','++TABLESPACE','++TEMPTABLESPACE') AS GATHER_LEVEL,
              tp1.database_id,
              tp1.database_name,
              TP1.tablespace_name,
              '' as level_name,
              '' as AUTOEXTENSIBLE,
              sum(tp1.maxsum_gb) as maxsum_gb,
              sum(tp1.sum_gb) as sum_gb,
              sum(tp1.free_gb) as free_gb,
              sum(tp1.used_gb) as used_gb,
              round(sum(tp1.used_gb) / sum(tp1.sum_gb), 6) * 100  as used_precent,
              round(sum(tp1.used_gb) / sum(tp1.maxsum_gb), 6) * 100  as used_of_max_precent
        from tmp_datafile tp1
        where 1=1
        group by tp1.database_id,tp1.database_name,tp1.gather_level, tp1.tablespace_name
        order by tp1.database_id,tp1.database_name,tp1.gather_level, tp1.tablespace_name
      ) t_database
 union all
 --数据文件的信息
 select '----'||tp0.gather_level,
        tp0.database_id,
        tp0.database_name,
        tp0.tablespace_name,
        tp0.datafile_name,
        tp0.autoextensible,
        tp0.maxsum_gb,
        tp0.sum_gb,
        tp0.free_gb,
        tp0.used_gb,
        tp0.used_precent,
        tp0.used_of_max_precent
 from tmp_datafile tp0