#############################################
# check_tablespace.sh
#############################################
#!/bin/ksh
. /etc/oracle.profile

sqlplus -s <<!
"/ as sysdba

-- Start der Formatierung der Ausgabe
set feed off
set lines 3000
set pages 3000
set trimspool on
set trimout on
column Tablespace format a30
column "Size MB" format 999,999,999,999
column "Used MB" format 999,999,999,999
column "Free MB" format 999,999,999,999
column "% Used" format 999.99
-- Ende der Formatierung der Ausgabe

-- Spool der Ausgabe in die tablespace.alert:
spool tablespace.alert

-- Start des SQL-Statements
select
tablespace_name "Tablespace",
round(bytes/1048576) "Size MB",
round(nvl(bytes-free,bytes)/1048576) "Used MB",
round(nvl(free,0)/1048576) "Free MB",
nvl(100*(bytes-free)/bytes,100) "% Used"
from(
select
ddf.tablespace_name,
sum(dfs.bytes) free,
ddf.bytes bytes
from (
select tablespace_name, sum(bytes) bytes
from dba_data_files
group by tablespace_name
) ddf,
dba_free_space dfs
where ddf.tablespace_name = dfs.tablespace_ name(+)
group by ddf.tablespace_name, ddf.bytes
)
-- Berechnung des Fuellgrades:
where nvl(100*(bytes-free)/bytes,100) > 90
;
-- Ende des SQL-Statements

spool off
exit
!
if [ `cat tablespace.alert|wc -l` -gt 0 ]
then
mailx -s "TABLESPACE voll" dba-group@ domain.de < tablespace.alert
fi