oracle脚本---表空间使用

#!/bin/bash
source /home/oracle/.bash_profile
sqlplus -s zabbix/oracle@orcl_s  > /tmp/tablespace.log<<EOF
set linesize 300 pagesize 1000
col "Status"   for a10
col "Name"     for a25
col "Type"     for a10
col "Extent"   for a15
col "Size (M)" for a20
col "Used (M)" for a20
col "Used %"   for a20
SELECT d.status "Status",
       d.tablespace_name "Name",
       d.contents "Type",
       d.extent_management "Extent",
       TO_CHAR(NVL(a.bytes / 1024 / 1024, 0), '99,999,990') "Size (M)",
       TO_CHAR(NVL(a.bytes - NVL(f.bytes, 0), 0) / 1024 / 1024,'999,999,999') "Used (M)",
       TO_CHAR(NVL((a.bytes - NVL(f.bytes, 0)) / a.bytes * 100, 0),'990.00') "Used %"
  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
 WHERE d.tablespace_name = a.tablespace_name(+)
   AND d.tablespace_name = f.tablespace_name(+)
   AND NOT
        (d.extent_management like 'LOCAL' AND d.contents like 'TEMPORARY')
   and d.tablespace_name <> 'UNDOTBS1'
Union
SELECT
       C.status "Status",
       C.tablespace_name "Name",
       C.contents "Type",
       C.extent_management "Extent",
       TO_CHAR(SPACE) "Size (M)",
       TO_CHAR(SPACE - NVL(FREE_SPACE, 0)) "Used (M)",
       TO_CHAR(ROUND((1 - NVL(FREE_SPACE, 0) / SPACE) * 100, 2)) "Used %"
          FROM (SELECT TABLESPACE_NAME,
                       ROUND(SUM(BYTES) / (1024 * 1024), 2) SPACE,
                       SUM(BLOCKS) BLOCKS,
                       status
                  FROM DBA_DATA_FILES
                 WHERE TABLESPACE_NAME LIKE 'UNDO%'
                 GROUP BY TABLESPACE_NAME,status) D,
               (SELECT A.TABLESPACE_NAME , FREEA+FREEB FREE_SPACE
                  FROM (SELECT TABLESPACE_NAME,
                       SUM(BYTES) / (1024 * 1024) FREEA
                  FROM DBA_FREE_SPACE
                 WHERE TABLESPACE_NAME LIKE 'UNDO%'
                 GROUP BY TABLESPACE_NAME) A,
                       (SELECT TABLESPACE_NAME,
                       SUM(BYTES) / (1024 * 1024) FREEB
                  FROM DBA_UNDO_EXTENTS
                 WHERE STATUS='EXPIRED'
                 GROUP BY TABLESPACE_NAME) B
                WHERE
                     A.TABLESPACE_NAME=B.TABLESPACE_NAME(+)) F,
              dba_tablespaces c
         WHERE D.TABLESPACE_NAME=C.TABLESPACE_NAME
                 AND D.TABLESPACE_NAME = F.TABLESPACE_NAME(+)
ORDER BY 7;
EOF

SELECT UPPER(D.TABLESPACE_NAME) "Tablespace_name",
D.TOT_GROOTTE_MB - nvl(F.TOTAL_BYTES,0) "Used(M)",
D.TOT_GROOTTE_MB "Tablespace_size(M)",
TO_CHAR(ROUND((D.TOT_GROOTTE_MB - nvl(F.TOTAL_BYTES,0)) / D.TOT_GROOTTE_MB * 100,
2),
'990.99') || '%' "Used%",
nvl(F.TOTAL_BYTES,0) "Available_size(M)",
decode(D.TOT_MAXBYTES_MB, 0, D.TOT_GROOTTE_MB, D.TOT_MAXBYTES_MB) "Max_Tablespace_size(M)",
decode(D.TOT_MAXBYTES_MB,
0,
TO_CHAR(ROUND((D.TOT_GROOTTE_MB - F.TOTAL_BYTES) /
D.TOT_GROOTTE_MB * 100,
2),
'990.99') || '%',
TO_CHAR(ROUND(((D.TOT_GROOTTE_MB - F.TOTAL_BYTES)) /
D.TOT_MAXBYTES_MB * 100,
2),
'990.99') || '%') "Max_Used%",
decode(D.TOT_MAXBYTES_MB, 0, D.TOT_GROOTTE_MB, D.TOT_MAXBYTES_MB) -
(D.TOT_GROOTTE_MB - nvl(F.TOTAL_BYTES,0)) "Max_Available_size(M)"
FROM (SELECT TABLESPACE_NAME,
ROUND(SUM(BYTES) / (1024 * 1024), 2) TOTAL_BYTES
FROM SYS.DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME) F,
(SELECT DD.TABLESPACE_NAME,
ROUND(SUM(DD.BYTES) / (1024 * 1024), 2) TOT_GROOTTE_MB,
ROUND(SUM( decode(DD.MAXBYTES,0,DD.BYTES,DD.MAXBYTES)) / (1024 * 1024), 2) TOT_MAXBYTES_MB
FROM SYS.DBA_DATA_FILES DD
GROUP BY DD.TABLESPACE_NAME) D
WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+)
ORDER BY decode(D.TOT_MAXBYTES_MB,
0,
TO_CHAR(ROUND((D.TOT_GROOTTE_MB - F.TOTAL_BYTES) /
D.TOT_GROOTTE_MB * 100,
2),
'990.99') || '%',
TO_CHAR(ROUND(((D.TOT_GROOTTE_MB - F.TOTAL_BYTES)) /
D.TOT_MAXBYTES_MB * 100,
2),
'990.99') || '%') desc;