实战:oracle新建表空间的shell脚本，oracleshell

实战:oracle新建表空间的shell脚本，oracleshell

#!/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" <<!01 >/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" <<!01 >/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" <<EOF 
	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

新建一个Command Window 在提示符下输入一下命令，回车执行就可以了
create tablespace dbspace datafile 'D:\oracle\product\10.2.0\oradata\orcl\dbspace.dbf' size 400M autoextend on next 10m maxsize unlimited;--创建表空间
DROP TABLESPACE dbspace INCLUDING CONTENTS AND DATAFILES;--删除表空间

其中
1) DATAFILE: 表空间数据文件存放路径
2) SIZE: 起初设置为200M
3) UNIFORM: 指定区尺寸为128k,如不指定，区尺寸默认为64k
4) 空间名称histdb 与 数据文件名称 histdb.dbf 不要求相同,可随意命名.
5) AUTOEXTEND ON/OFF 表示启动/停止自动扩展表空间
6) alter database datafile ' D:\oracle\product\10.2.0\oradata\orcl\histdb.dbf ' resize 500m; //手动修改数据文件大小为

下列命令用于为表空间建立用户，将用户的默认表空间设置为刚建立的表空间
create user test1 identified by test1 default tablespace dbspace;
alter database default tablespace dbspace;
create user test identified by test;
select username, default_tablespace defspace from dba_users where username='TEST';
 

如果只是学会，我相信没什么好炫耀的，因为linux+oracle+shell，基本的我都会。建议你精通一到两个就可以了。其他略懂略懂，相信这样的话，你就可以再未来的企业里很好滴发展了。

linux+oralce是很多大型企业用的很多的操作系统和数据库，精通了这两门，你过万月薪根本没问题。加油吧GG或MM