oracle表空间监控shell,实战:oracle新建表空间的shell脚本

#!/bin/bash

#ocpyang@126.com

#create tablespace

if [ $# -ne 2 ]; then

echo "Usage: $0 TABLESPACE_NAME TABLESPACE_SIZE"

exit 1

fi

#configure oracle env:about oracle envs, username and password

ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1

ORACLE_SID=orcl

ora_data=/u01/app/oracle/product/11.2.0/db_1/dbs/

ora_user="sys"

ora_pass="password"

tablespace_name=$(echo $1 | tr '[a-z]' '[A-Z]')

tablespace_size=$2

outfiletmp01=/tmp/createtpstmp01.txt #specify the output file location

outfiletmp03=/tmp/createtpstmp03.txt #specify the output file location

outfiletmp02=/tmp/createtpstmp02.txt #specify the output file location

#check oracle instance is down or up

sqlplus -S "${ora_user}/${ora_pass} as sysdba" </dev/null #禁止sqlplus执行结果回显

set heading off;

set feedback off;

set termout off;

set pagesize 0;

set verify off;

set echo off;

spool ${outfiletmp01}

select sysdate from dual;

spool off

exit;

!01

ins_jug=`grep -i "ORA-01034:" ${outfiletmp01} >${outfiletmp02} `

if [ -s ${outfiletmp02} ]; then

echo -e "\e[1;31m ****************************************************************** \e[0m"

echo -e "\e[1;31m !!!!, oracle IS down! \e[0m"

echo -e "\e[1;31m ****************************************************************** \e[0m"

rm -rf ${outfiletmp01}

rm -rf ${outfiletmp02}

exit 1

fi

#

sqlplus -S "${ora_user}/${ora_pass} as sysdba" </dev/null #禁止sqlplus执行结果回显

set heading off;

set feedback off;

set termout off;

set pagesize 0;

set verify off;

set echo off;

spool $outfiletmp03

select tablespace_name from dba_tablespaces where tablespace_name='${tablespace_name}';

spool off

exit;

!01

tps_jug=`grep -i ${tablespace_name} ${outfiletmp03} `

if [ "${tps_jug}" = "${tablespace_name}" ]; then

echo -e "\e[1;32m The tablespace ${tablespace_name} exits! \e[0m"

rm -rf ${outfiletmp03}

exit 1

else

wind_var=$(

sqlplus -s "{ora_user}/${ora_pass} as sysdba" <

create tablespace ${tablespace_name}

datafile '${ora_data}/${tablespace_name}.dbf'

size $tablespace_size

extent management local

uniform size 128k

segment space management auto;

EXIT ;

EOF)

echo -e "\e[1;32m ${wind_var} \e[0m" #Direct display returns results

rm -rf ${outfiletmp03}

exit 1

fi