Why ?
To
check the tablespace status in a script file that is return a value of 0 OK or 1
not OK a long with Tablespaces details , I am using the below bin bash script
which is show all needed details and I configured a waring for 12GB then error
for 9GB otherwise all are ok , you may change this upon needed .
How ?
Below
is bin bash script and I am assuming you make sure to set the profile things in
the running user :
1- create and set up the script file:
vi new_check_tablespace.sh
chmod +x new_check_tablespace.sh
2- in the script.sh file :
#!/bin/bash
export PATH=/usr/bin:/usr/sbin
export ORACLE_HOME=/u01/app/oracle/product/19.0.0/dbhome_1
export ORACLE_SID=ORCL
export PATH=$PATH:$ORACLE_HOME/bin
check_tbspace() {
temp1=`sqlplus -s / as sysdba <<EOF
set serveroutput on;
SET FEEDBACK OFF
DECLARE
temp integer ;
CURSOR table_space IS
SELECT tablespace_name,
size_mb,
free_mb,
max_size_mb,
max_free_mb,
TRUNC((max_free_mb/max_size_mb) * 100) AS free_pct,
RPAD(' '|| RPAD('X',ROUND((max_size_mb-max_free_mb)/max_size_mb*10,0), 'X'),11,'-') AS used_pct
FROM (
SELECT a.tablespace_name,
b.size_mb,
a.free_mb,
b.max_size_mb,
a.free_mb + (b.max_size_mb - b.size_mb) AS max_free_mb
FROM (SELECT tablespace_name,
TRUNC(SUM(bytes)/1024/1024) AS free_mb
FROM dba_free_space
GROUP BY tablespace_name) a,
(SELECT tablespace_name,
TRUNC(SUM(bytes)/1024/1024) AS size_mb,
TRUNC(SUM(GREATEST(bytes,maxbytes))/1024/1024) AS max_size_mb
FROM dba_data_files
GROUP BY tablespace_name) b
WHERE a.tablespace_name = b.tablespace_name
)
ORDER BY tablespace_name;
t_row table_space%ROWTYPE;
begin
temp := 0;
open table_space ;
loop
fetch table_space into t_row ;
exit when table_space%NOTFOUND;
if ( t_row.MAX_FREE_MB <=12288) and (temp <> 1) then
temp :=1;
DBMS_OUTPUT.PUT_LINE(1);
elsif ( t_row.MAX_FREE_MB <= 119216) and (temp <> 1) then
temp :=2;
DBMS_OUTPUT.PUT_LINE(2);
end if;
END LOOP;
if temp = 0 then
dbms_output.put_line(0);
end if;
close table_space;
END;
/
EXIT;
EOF`
}
tbspace_details() {
sqlplus -s / as sysdba <<EOF
SET PAGESIZE 140 LINESIZE 200;
COLUMN used_pct FORMAT A11;
SELECT tablespace_name,
size_mb,
free_mb,
max_size_mb,
max_free_mb,
TRUNC((max_free_mb/max_size_mb) * 100) AS free_pct,
RPAD(' '|| RPAD('X',ROUND((max_size_mb-max_free_mb)/max_size_mb*10,0), 'X'),11,'-') AS used_pct
FROM (
SELECT a.tablespace_name,
b.size_mb,
a.free_mb,
b.max_size_mb,
a.free_mb + (b.max_size_mb - b.size_mb) AS max_free_mb
FROM (SELECT tablespace_name,
TRUNC(SUM(bytes)/1024/1024) AS free_mb
FROM dba_free_space
GROUP BY tablespace_name) a,
(SELECT tablespace_name,
TRUNC(SUM(bytes)/1024/1024) AS size_mb,
TRUNC(SUM(GREATEST(bytes,maxbytes))/1024/1024) AS max_size_mb
FROM dba_data_files
GROUP BY tablespace_name) b
WHERE a.tablespace_name = b.tablespace_name
)
ORDER BY free_pct ;
exit;
EOF
}
status() {
check_tbspace
tbspace_details
if [ "$temp1" == "1" ]; then
echo "**Warning NOT All TableSpaces have free space more than 12GB "
return 1
elif [ "$temp1" == "2" ]; then
echo "--ERROR One or more TableSpaces have free space less than 9GB "
return 1
else
echo "++OK All TableSpaces have free space more than 10GB "
return 0
fi
}
###################### END "STATUS" SECTION ##############################
case "$1" in
'status')
status
;;
'root')
su oracle -c "cd /u01/app/monit/script && ./new_check_tablespace.sh status"
;;
*)
echo $"Usage: $0 {status}"
exit 1
esac
exit 0
