检测 10g 11g 数据库大小 使用率 的 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)*/
        'TEMPFILE' 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