在日常的DBA管理中,shell的使用可以快速、高效、安全的进行一些维护工作。下面分享下笔者迁移数据库环节中的一些脚本,以供学习交流。

 

#!/bin/sh
#Note
#Author:LengBo
#Time:20120423
#Purpose:For EXP/IMP
#conn_str=vm_fsxybak
conn_str=peppdb
echo  “Please enter you username with DBA privilegs,default system:”
read username
echo “Username is: $username”
echo  “Please enter you password:”
read password
echo “Password Is:$password”
echo -n “Enter you choice: y[yes]; n[no]:”
sleep 10

read choice
case $choice in
y|yes)
     result=1
;;
n|no)
     result=0
;;
esac

if [ $result -ne 0 ]
   then
source ~/.bash_profile
sqlplus  $username/$password@${conn_str}  <<EOF
set head off;
set echo off;
set lin 200;
spool ‘/tmp/dis_pk.sql’;

select ‘ alter table  ‘||s.owner||’.’|| table_name  ||’ disable primary key cascade;’
from dba_constraints s
where s.constraint_type=’P’
and s.owner  in(‘PEPP_COMMON’,’PEPP_TAPS’,’PEPP_PE’,’PEPP_CUSTSVC’,’PEPP_BMS’,’PEPP_STT’,’PEPP_RISKMAN’,’PEPP_B2B’,’PEPP_WP’)
and s.owner not in(‘SYS’,’SYSTEM’,’SYSMAN’);

spool off;
EOF

more /tmp/dis_pk.sql | sed ‘/^$/d’ | sed ‘1d’|sed ‘1d’ |more |sed ‘$d’|sed ‘$d’ > /tmp/tmp.sql

sqlplus  $username/$password@${conn_str}  <<EOF
set timing on;
prompt Now Staring Disable All triggers:
set timing on
execute dbms_lock.sleep(5);
@/tmp/tmp.sql;
EOF

if [ $? -eq 0 ]
    then echo “Note:Disable PK Succeed!”
fi

cd /tmp && rm *.sql
else
   echo “Not proceding,Now exiting!”
   exit
fi