Search This Blog

Total Pageviews

Saturday 1 May 2010

oracle tablespace cron job

-bash-3.00$ cat /export/home/oracle/scripts/space.sh
#!/bin/bash
export ORACLE_BASE=/opt/oracle
export ORACLE_HOME=/opt/oracle/product/10.2
export PATH=$ORACLE_HOME/bin:.:$PATH
export ORACLE_SID=cccdb

sqlplus -s /nolog <connect / as sysdba
set pagesize 100 lines 120
set feed off heading off
break on REPORT;
compute sum label 'Total' of SIZE_MB on REPORT;
compute sum label 'Total Free MB' of FREE_MB on REPORT;
compute sum label 'Total Used MB' of USED_MB on REPORT;
COLUMN dummy NOPRINT
COLUMN pct_used FORMAT 999.9 HEADING "%|Used"
COLUMN name FORMAT a21 HEADING "Tablespace Name"
COLUMN Kbytes FORMAT 999,999,999 HEADING "MBytes"
COLUMN used FORMAT 999,999,999 HEADING "Used"
COLUMN free FORMAT 999,999,999 HEADING "Free"
COLUMN largest FORMAT 999,999,999 HEADING "Largest"
COLUMN maxsize FORMAT 999,999,999 HEADING "MAXSIZE"
BREAK ON report
COMPUTE sum OF kbytes ON REPORT
COMPUTE sum OF free ON REPORT
COMPUTE sum OF used ON REPORT

spool tablespace1.alert
select '********************************************' from dual;
select 'TABLESPACE REPORT1 PRODUCTION DATABASE ' from dual;
select to_char(sysdate, 'Dy DD-Mon-YYYY HH24:MI:SS') as "Report Time" from dual;
select '********************************************' from dual;
set feed on heading on
SELECT
NVL(b.tablespace_name,nvl(a.tablespace_name,'UNKOWN')) name
, kbytes_alloc/1024 kbytes
, nvl(kbytes_alloc/1024,0) - NVL(kbytes_free/1024,0) used
, NVL(kbytes_free,0) free
, ((kbytes_alloc-NVL(kbytes_free,0))/kbytes_alloc)*100 pct_used
, NVL(largest,0) largest
,MAXBYTES/1024/1024 MAXSIZE
,AUTOEXTENSIBLE
FROM ( SELECT SUM(bytes)/1024 Kbytes_free
, MAX(bytes)/1024 largest
, tablespace_name
FROM sys.dba_free_space
GROUP BY tablespace_name
) a
, ( SELECT SUM(bytes)/1024 Kbytes_alloc
, tablespace_name,AUTOEXTENSIBLE,MAXBYTES
FROM sys.dba_data_files
GROUP BY tablespace_name,AUTOEXTENSIBLE,MAXBYTES
) b
WHERE a.tablespace_name (+) = b.tablespace_name
order by 1;

spool off
exit
EOF
if [ `cat tablespace1.alert|wc -l` -gt 0 ]
then
# echo "test"
cat tablespace1.alert > tablespace1.tmp
# mailx -s "TABLESPACE ALERT for Production Database" -r oracle@joshua anuj.@tus.co.uk < tablespace1.tmp
#/usr/lib/sendmail -s "TABLESPACE ALERT1 for Joshua" anuj.singh@pus.co.uk < tablespace1.tmp

#cat tablespace.tmp | mailx -s "mail test" anuj.s@ptus.co.uk
fi

No comments:

Oracle DBA

anuj blog Archive