Expandmenu Shrunk


  • Category Archives Shell
  • 分享一Oracle脚本

    在日常的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




香港马会开奖记录|香港马会开奖资料|香港马会开奖现场|香港马会走势图|香港马会开奖结果直播|香港马会n730|