Hi ,
i have a shell script and Sql Script for tablespace report as follows
tbsp_all.sh
#!/bin/bash
for DB in $(grep N$ /etc/oratab | cut -d: -f 1)
do
INST=$(echo "$DB" |tr 'A-Z' 'a-z')
oracle_user=ora$INST
sudo su - "$oracle_user"<<EOO
sqlplus /nolog<<EOS
connect / as sysdba
column tablespace_name format A10
@ /usr/local/sbin/dbscripts/tbsp.sql
EOS
EOO
done
exit 0
Tbsp.sql
-------------
set linesize 172
define history_days=100
define threshold_pct=0
set verify off
SELECT INSTANCE_NAME FROM v$instance;
SELECT CONTENTS,tablespace_name,
CEIL(SUM(used)/POWER(1024,3)) USED_GB,
CEIL(SUM(total)/POWER(1024,3)) MAXSIZE_GB,
TO_CHAR(100*SUM(used)/SUM(total),'999')||' %' Pct_Used,
CEIL(SUM(total)/POWER(1024,3))-CEIL(SUM(used)/POWER(1024,3)) FREE_SPACE,
ROUND(TO_CHAR((SUM(nvl2(creation,used,0)))/POWER(1024,3)),2) GROWTH_GB,
TO_CHAR(100*((SUM(nvl2(creation,used,0))))/SUM(total),'S99999')||'% in last &&history_days days' GROWTH_PCT
FROM (
SELECT what,tablespace_name,file_name,bytes_used used,
CASE WHEN autoextensible='NO' THEN bytes
WHEN NVL(maxbytes,0) < bytes THEN bytes
ELSE maxbytes
END total
FROM (
SELECT 'tempfile' what,file_id,tablespace_name,file_name,relative_fno,bytes,autoextensible,maxbytes,bytes_used
FROM V$temp_space_header s join DBA_TEMP_FILES f USING (file_id,tablespace_name,relative_fno)
WHERE status ='AVAILABLE'
UNION ALL
SELECT 'datafile' what,file_id,tablespace_name,file_name,relative_fno,f.bytes,autoextensible,maxbytes,
f.bytes-NVL(s.bytes,0) bytes_used
FROM DBA_DATA_FILES f left outer join (
SELECT file_id,tablespace_name,relative_fno,SUM(bytes) bytes
FROM DBA_FREE_SPACE GROUP BY file_id,tablespace_name,relative_fno
) s USING (file_id,tablespace_name,relative_fno)
WHERE status ='AVAILABLE'
) ) join DBA_TABLESPACES USING (tablespace_name)
left outer join (
SELECT name file_name , SYSDATE-creation_time creation FROM v$datafile WHERE creation_time > SYSDATE - &&history_days )
USING (file_name) HAVING SUM(used)/SUM(total) > ( &&threshold_pct / 100)
GROUP BY CONTENTS,tablespace_name ORDER BY 3 DESC;
set verify on
My above Shell Script gives me the below output
INSTANCE_NAME
----------------
MEA
CONTENTS TABLESPACE USED_GB MAXSIZE_GB PCT_US FREE_SPACE GROWTH_GB GROWTH_PCT
--------- ---------- ---------- ---------- ------ ---------- ---------- ------------------------
PERMANENT PSAPMEA 67 75 90 % 8 2.79 +4% in last 100 days
PERMANENT PSAPMEA702 42 59 71 % 17 0 +0% in last 100 days
PERMANENT PSAPMEADB 4 5 72 % 1 0 +0% in last 100 days
PERMANENT SYSAUX 2 10 16 % 8 0 +0% in last 100 days
PERMANENT SYSTEM 2 20 5 % 18 0 +0% in last 100 days
PERMANENT PSAPMEAUSR 1 1 28 % 0 0 +0% in last 100 days
UNDO PSAPUNDO 1 10 3 % 9 0 +0% in last 100 days
7 rows selected.
SQL> Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL*Plus: Release 11.2.0.3.0 Production on Tue Jul 29 00:44:48 2014
Copyright (c) 1982, 2011, Oracle. All rights reserved.
SQL> Connected.
SQL> SQL>
INSTANCE_NAME
----------------
GEA
CONTENTS TABLESPACE USED_GB MAXSIZE_GB PCT_US FREE_SPACE GROWTH_GB GROWTH_PCT
--------- ---------- ---------- ---------- ------ ---------- ---------- ------------------------
PERMANENT PSAPGXA 279 342 81 % 63 0 +0% in last 100 days
PERMANENT PSAPGXA702 19 49 37 % 30 0 +0% in last 100 days
PERMANENT PSAPGXJDB 3 49 5 % 46 0 +0% in last 100 days
PERMANENT SYSTEM 2 10 16 % 8 0 +0% in last 100 days
PERMANENT SYSAUX 1 10 9 % 9 0 +0% in last 100 days
UNDO PSAPUNDO 1 98 0 % 97 0 +0% in last 100 days
PERMANENT PSAPGXAUSR 1 10 0 % 9 0 +0% in last 100 days
'
'
'
and so on for all DB's on Server,
By using the above o/p or script i created another script for mail alert
#!/bin/bash
ADMIN="srinivas.avunuri@accenture.com"
# set alert level 90% is default
ALERT=90
# store all disk info here
> /tmp/output.txt
echo "Tablespace utilisation on $(hostname) as on $(date)" > /tmp/output.txt
echo "" >> /tmp/output.txt
EMAIL=0
/dbscripts/tbsp_all.sh | grep -vE '^Filesystem|tmpfs|cdrom' | awk '{ print $2 " " $5}' | while read output;
do
#echo $output
usep=$(echo $output | awk '{ print $2}' | cut -d'%' -f1 )
partition=$(echo $output | awk '{ print $1 }' )
if [ $usep -ge $ALERT ]; then
echo "* $partition ($usep%) *" >> /tmp/output.txt
EMAIL=1
mailx -s "Alert: Tablespace Utilisation" $ADMIN < /tmp/output.txt
fi
done
rm /tmp/output.txt
Which is giving me error
-bash: [: -ge: unary operator expected
-bash: [: -ge: unary operator expected
-bash: [: PCT_US: integer expression expected
-bash: [: ------: integer expression expected
-bash: [: -ge: unary operator expected
-bash: [: -ge: unary operator expected
My guess is because of BOLD Text(i modified please see in above output), its trying to find integer colums but Strings are also included in output.txt , if we remove string keeping only colums then we can get expected output.
Can you please resolve this and also please tell me in this script how can get SID in mail alert(for which tablespace reaches to threshold)
(Or)
if you have good script for the same please send me / mail me at srinivas.avunuri@accenture.com/oradba.srini@gmail.com