通过Shell脚本查看数据库表空间使用情况

对于数据库中表空间查看，想必大家都有很多的脚本已经在用了，自己也啰嗦一下，分享一个通过shell脚本查看表空间使用情况的例子。

对于数据库中表空间查看，想必大家都有很多的脚本已经在用了，，自己也啰嗦一下，分享一个通过shell脚本查看表空间使用情况的例子。
 脚本如下：
sqlplus -s $DB_CONN_STR@$SH_DB_SID   set echo off heading on underline on;
 column inst_num  heading "Inst Num"  new_value inst_num  format 99999;
 column inst_name heading "Instance"  new_value inst_name format a12;
 column db_name  heading "DB Name"  new_value db_name  format a12;
 column dbid      heading "DB Id"    new_value dbid      format 9999999999 just c;

 prompt
 prompt Current Instance
 prompt ~~~~~~~~~~~~~~~~

 select d.dbid            dbid
      , d.name            db_name
      , i.instance_number inst_num
      , i.instance_name  inst_name
  from v\$database d,
        v\$instance i;
       
 set term on feedback off lines 130 pagesize 999 tab off trims on
 column MB format 999,999,999  heading "Total MB"
 column free format 9,999,999 heading "Free MB"
 column used format 99,999,999 heading "Used MB"
 column Largest format 999,999 heading "LrgstMB"
 column tablespace_name format a20 heading "Tablespace"
 column status format a3 truncated
 column max_extents format 99999999999 heading "MaxExt"
 col extent_management          for a1 trunc  head "M"
 col allocation_type            for a1 trunc  head "A"
 col Ext_Size for a4 trunc head "Init"
 column pfree format a3 trunc heading "%Fr"

 break on report
 compute sum of MB on report
 compute sum of free on report
 compute sum of used on report

 select 
  d.tablespace_name,
  decode(d.status,
    'ONLINE', 'OLN',
    'READ ONLY', 'R/O',
    d.status) status,
  d.extent_management,
  decode(d.allocation_type,
    'USER','',
    d.allocation_type) allocation_type,
  (case
    when initial_extent     then lpad(round(initial_extent/1024,0),3)||'K'
    else lpad(round(initial_extent/1024/1024,0),3)||'M'
  end) Ext_Size,
  NVL (a.bytes / 1024 / 1024, 0) MB,
  NVL (f.bytes / 1024 / 1024, 0) free,
  (NVL (a.bytes / 1024 / 1024, 0) - NVL (f.bytes / 1024 / 1024, 0)) used,
  NVL (l.large / 1024 / 1024, 0) largest,
  d.MAX_EXTENTS ,
  lpad(round((f.bytes/a.bytes)*100,0),3) pfree,
  (case when round(f.bytes/a.bytes*100,0) >= 20 then ' ' else '*' end) alrt
 FROM sys.dba_tablespaces d,
  (SELECT  tablespace_name, SUM(bytes) bytes
    FROM dba_data_files
    GROUP BY tablespace_name) a,
  (SELECT  tablespace_name, SUM(bytes) bytes
    FROM dba_free_space
    GROUP BY tablespace_name) f,
  (SELECT  tablespace_name, MAX(bytes) large
    FROM dba_free_space
    GROUP BY tablespace_name) l
 WHERE d.tablespace_name = a.tablespace_name(+)
  AND d.tablespace_name = f.tablespace_name(+)
  AND d.tablespace_name = l.tablespace_name(+)
  AND NOT (d.extent_management LIKE 'LOCAL' AND d.contents LIKE 'TEMPORARY')
 UNION ALL
 select
  d.tablespace_name,
  decode(d.status,
    'ONLINE', 'OLN',
    'READ ONLY', 'R/O',
    d.status) status,
  d.extent_management,
  decode(d.allocation_type,
    'UNIFORM','U',
    'SYSTEM','A',
    'USER','',
    d.allocation_type) allocation_type,
  (case
    when initial_extent     then lpad(round(initial_extent/1024,0),3)||'K'
    else lpad(round(initial_extent/1024/1024,0),3)||'M'
  end) Ext_Size,
  NVL (a.bytes / 1024 / 1024, 0) MB,
  (NVL (a.bytes / 1024 / 1024, 0) - NVL (t.bytes / 1024 / 1024, 0)) free,
  NVL (t.bytes / 1024 / 1024, 0) used,
  NVL (l.large / 1024 / 1024, 0) largest,
  d.MAX_EXTENTS ,
  lpad(round(nvl(((a.bytes-t.bytes)/NVL(a.bytes,0))*100,100),0),3) pfree,
  (case when nvl(round(((a.bytes-t.bytes)/NVL(a.bytes,0))*100,0),100) >= 20 then ' ' else '*' end) alrt
 FROM sys.dba_tablespaces d,
  (SELECT  tablespace_name, SUM(bytes) bytes
    FROM dba_temp_files
    GROUP BY tablespace_name order by tablespace_name) a,
  (SELECT  tablespace_name, SUM(bytes_used  ) bytes
    FROM v\$temp_extent_pool
    GROUP BY tablespace_name) t,
  (SELECT  tablespace_name, MAX(bytes_cached) large
    FROM v\$temp_extent_pool
    GROUP BY tablespace_name order by tablespace_name) l
 WHERE d.tablespace_name = a.tablespace_name(+)
  AND d.tablespace_name = t.tablespace_name(+)
  AND d.tablespace_name = l.tablespace_name(+)
  AND d.extent_management LIKE 'LOCAL'
  AND d.contents LIKE 'TEMPORARY'
  ORDER by 1
 /
 prompt
 exit
 EOF

运行结果相对比较简单，也很清晰。

本文永久更新链接地址：