實戰:oracle刪除資料表空間的shell指令碼，oracleshell

實戰:oracle刪除資料表空間的shell指令碼，oracleshell

#!/bin/bash
#ocpyang@126.com
#drop tablespace

if [ $# -ne 1 ]; then
  echo "Usage: $0 TABLESPACE_NAME "
  exit 1
fi

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

ORACLE_SID=orcl

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

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

ora_user="sys"  #oracle username

ora_pass="password"  #oracle user password

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

outfiletmp=/tmp/droptpstmp01.txt  #specify the output file location

 

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 ${outfiletmp}  
select tablespace_name from dba_tablespaces where tablespace_name='${tablespace_name}';
spool off
exit;
!01

 

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

 

if [ "${tps_jug}" = "${tablespace_name}" ]; then
 wind_var=$(
 sqlplus -s "{ora_user}/${ora_pass} as sysdba" <<EOF
 set heading off
 drop tablespace ${tablespace_name} including contents and datafiles;
 EXIT;
 EOF)
 echo -e "\e[1;32m  ${wind_var} \e[0m"   #Direct display returns results
 rm -rf ${outfiletmp}
 exit 1
else
 echo -e "\e[1;31m --------------------------------------- \e[0m"
 echo -e "\e[1;31m  The tablespace ${tablespace_name} not exits! \e[0m"
 echo -e "\e[1;31m --------------------------------------- \e[0m"
 rm -rf ${outfiletmp}
 exit 1

fi

 

 

 

 

怎徹底刪除oracle建立的使用者與資料表空間

如果刪除使用者使用drop即可，如果要刪除資料表空間的同時，刪除對應的檔案可以這樣drop tablespace ocscdrbak including contents;drop tablespace tablespace_name including contents and datafiles;
windows 要先offline。
 
oracle 10g 怎徹底刪除有資料的資料表空間後，在重建資料表空間？

drop tablespace tbsname include contents and datafiles;
然後再重建立立就可以了
create tablespace tbsname datafile '檔案路徑及檔案名稱' size 500m;