(shell)表空间监控

[oracle@centos6 scripts]$ cat showtps.sh
sqlplus -s $DB_CONN_STR  <<EOF
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 < 1048576 
    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 < 1048576 
    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

运行效果：

[oracle@centos6 scripts]$ sh showtps.sh

Current Instance
~~~~~~~~~~~~~~~~

   DB Id    DB Name      Inst Num Instance
----------- ------------ -------- ------------
 1448896496 ORCL                1 orcl


Tablespace           STA M A Init     Total MB    Free MB     Used MB  LrgstMB       MaxExt %Fr ALR
-------------------- --- - - ---- ------------ ---------- ----------- -------- ------------ --- ---
EXAMPLE              OLN L S  64K          343         33         310       31   2147483645  10 *
LOB_DATA             OLN L S  64K           10          9           1        9   2147483645  88
SYSAUX               OLN L S  64K          560         36         524       36   2147483645   6 *
SYSTEM               OLN L S  64K          760         10         750        9   2147483645   1 *
TEMP                 OLN L U   1M           29         28           1       28               97
UNDOTBS1             OLN L S  64K          105         74          31       62   2147483645  70
USERS                OLN L S  64K            5          1           4        1   2147483645  14 *
                                  ------------ ---------- -----------
sum                                      1,812        190       1,622