指令碼：線上重定義，從普通表到分割槽表，redefinition_table.sh

raysuen發表於2018-03-09
指令碼
使用方法：
    redefinition_table.sh -o 普通表 -i 分割槽表 -u 表所在的使用者名稱 -f 使用哪個方式重定義:[rowid|pk] -a 動作[redefine] -c oracle連線字串:ora_name/ora_pass@tnsname
    -c 如果連線本地資料庫可以不指定，預設為/ as sysdba
    -f 提供rowid或者pk兩個值，預設為pk
    -a 表示需要執行那個動作，重定義還是終止定義。預設：redefine


點選(此處)摺疊或開啟

  1. #!/bin/bash
  2. #by raysuen
  3. #version 2.0


  6. [ -e ~/.profile ] && . ~/.profile
  7. [ -e ~/.bash_profile ] && . ~/.bash_profile


  10. ###################################################
  11. #The function is check that the User exists in the database.
  12. ###################################################
  13. CheckUser(){
  14.     sqlplus -s /nolog<<-RAY
  15.         set termout off;
  16.         set echo off;
  17.         set feedback off;
  18.         set verify off;
  19.         set heading off;
  20.         conn $2
  21.         select username from dba_users where username='$1';
  22.     RAY
  23. }


  26. ###################################################
  27. #The function is check that the table exists in the database.
  28. ###################################################

  30. CheckTable(){
  31.     sqlplus -s /nolog<<-RAY
  32.         set termout off;
  33.         set echo off;
  34.         set feedback off;
  35.         set verify off;
  36.         set heading off;
  37.         conn $3
  38.         select table_name from dba_tables where owner='$1' and table_name='$2';
  39.     RAY
  40. }

  42. ###################################################
  43. #The function is check that can redefinition table.
  44. ###################################################
  45. CanRedef(){
  46.     sqlplus -s /nolog<<-RAY
  47.         conn $1
  48.         set termout off;
  49.         set echo off;
  50.         set verify off;
  51.         set heading off;
  52.         $2
  53.     RAY
  54. }

  56. ###################################################
  57. #The function that redefinition table
  58. ###################################################
  59. redefinitionTable(){
  60.     sqlplus -s /nolog<<-RAY
  61.         conn $4
  62.         set termout off;
  63.         set echo off;
  64.         set verify off;
  65.         set heading off;
  66.         alter session force parallel dml;
  67.         alter session force parallel query;
  68.         $5
  69.         DECLARE
  70.             num_errors PLS_INTEGER;
  71.         BEGIN
  72.               DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS('$1', '$2','$3',
  73.                DBMS_REDEFINITION.CONS_ORIG_PARAMS, TRUE, TRUE, TRUE, TRUE, num_errors);
  74.         END;
  75.         /
  76.         exec dbms_redefinition.sync_interim_table(uname => '$1',orig_table => '$2',int_table => '$3');
  77.         exec dbms_redefinition.finish_redef_table(uname => '$1',orig_table => '$2',int_table => '$3');
  78.     RAY
  79. }

  81. ###################################################
  82. #The function that abort table
  83. ###################################################
  84. AbortTable(){
  85.     sqlplus -s /nolog<<-RAY
  86.         conn $4
  87.         set termout off;
  88.         set echo off;
  89.         set verify off;
  90.         set heading off;
  91.         DBMS_REDEFINITION.ABORT_REDEF_TABLE($1,$2,$3)
  92.     RAY
  93. }


  96. ###################################################
  97. #Checkt to chennect oracle
  98. ###################################################
  99. CheckConnect(){
  100.     sqlplus /nolog<<-RAY
  101.         conn $1
  102.     RAY
  103. }



  107. ###################################################
  108. #The function for help
  109. ###################################################

  111. func_help(){
  112.     echo "Example:"
  113.     echo "    redefinition_table.sh -o origins_table -i interlayer_table -u user_name -f option_flag -a action -c ora_user/ora_passwd@tnsname"
  114.     echo "Value:"
  115.     echo "    -c default value [/ as sysdba]"
  116.     echo "    -f to use the way which redefine table."
  117.     echo "        value: rowid/pk,default value: PK."
  118.     echo "    -a specify a action which you want to do,default: redefine"
  119.     
  120. }



  124. ###################################################
  125. #Entrance of the script
  126. ###################################################

  128. ###################################################
  129. #get parameter
  130. ###################################################
  131. if [ $# -lt 1 ];then
  132.     echo "no parameters"
  133.     exit 99
  134. else
  135.     while (($#>=1))
  136.     do
  137.         #echo $#
  138.         if [ $1 == "-o" ];then #-o origins
  139.             shift
  140.             orig_table=`echo $1 | tr [a-z] [A-Z]`
  141.             shift
  142.         elif [ $1 == "-i" ];then #-i interlayer
  143.             shift
  144.             int_table=`echo $1 | tr [a-z] [A-Z]`
  145.             shift
  146.         elif [ $1 == "-u" ];then
  147.             shift
  148.             user_name=`echo $1 | tr [a-z] [A-Z]`
  149.             shift
  150.         elif [ $1 == "-c" ];then #conect string include ora_username/ora_pwd@tns_name
  151.             shift
  152.             oraConStr=`echo $1`
  153.             shift
  154.         elif [ $1 == "-f" ];then #option_flag default: dbms_redefinition.cons_use_pk. values: rowid/PK
  155.             shift
  156.             option_flag=`echo $1 | tr [a-z] [A-Z]`
  157.             shift
  158.         elif [ $1 == "-a" ];then #action, the value must be redifine/abort. default: redifine
  159.             shift
  160.             action=`echo $1 | tr [a-z] [A-Z]`
  161.             shift
  162.         elif [ $1 == "-h" ];then
  163.             func_help
  164.             exit 0
  165.         else
  166.             echo "Please enter right parameter!"
  167.             echo "-h,you can use the parameter to get help!"
  168.             exit 99
  169.         fi
  170.     done
  171. fi

  173. #echo $orig_table
  174. #echo $int_table
  175. #echo $user_name

  177. ###################################################
  178. #Check that the value of orig_table
  179. ###################################################
  180. if [ ! ${orig_table} ];then
  181.     echo "-o must be specified!"
  182.     exit 1
  183. fi
  184. ###################################################
  185. #Check that the value of int_table
  186. ###################################################
  187. if [ ! ${int_table} ];then
  188.     echo "-i must be specified!"
  189.     exit 2
  190. fi
  191. ###################################################
  192. #Check that the value of user_name
  193. ###################################################
  194. if [ ! ${user_name} ];then
  195.     echo "-u must be specified!"
  196.     exit 3
  197. fi

  199. ###################################################
  200. #Check whether is successful to connecting oracle
  201. ###################################################
  202. [ ! ${oraConStr} ]&& oraConStr=' / as sysdba'

  204. CheckConnect "${oraConStr}" | grep "Connected" > /dev/null 2>&1
  205. if [ $? -ne 0 ];then
  206.         echo "it is fail to connect oracle using input connection string!"
  207.         exit 4
  208. fi

  210. ###################################################
  211. #Check whether the input user exists in the database.
  212. ###################################################
  213. if [ ! `CheckUser ${user_name} ${oraConStr} | sed 's/\n//g'` ];then
  214.     echo "User:("${user_name}") dose not exists in database!"
  215.     exit 5s
  216. fi

  218. ###################################################
  219. #Check whether the input table exists in the database.
  220. ###################################################
  221. if [ ! `CheckTable ${user_name} ${orig_table} ${oraConStr} | sed 's/\n//g'` ];then
  222.     echo "Table:("${orig_table}") dose not exists in database!"
  223.     exit 6
  224. fi
  225. if [ ! `CheckTable ${user_name} ${int_table} ${oraConStr} | sed 's/\n//g'` ];then
  226.     echo "Table:("${int_table}") dose not exists in database!"
  227.     exit 7
  228. fi


  231. ###################################################
  232. #Check the value of action be specified
  233. ###################################################
  234. [ ! ${action} ] && action="REDEFINE"

  236. ###################################################
  237. #Check whether can redefinition table.
  238. ###################################################
  239. if [ ${action} == 'REDEFINE' ];then
  240.     if [ ! ${option_flag} ];then
  241.         option_flag="PK"
  242.         canRedStr="exec dbms_redefinition.can_redef_table(""'"$user_name"'"",""'"$orig_table"'"");"
  243.         startRedStr="exec DBMS_REDEFINITION.start_redef_table(uname => '"$user_name"',orig_table => '"$orig_table"',int_table => '"${int_table}"');"
  244.     elif [ ${option_flag} == "PK" ];then
  245.         canRedStr="exec dbms_redefinition.can_redef_table(""'"$user_name"'"",""'"$orig_table"'"");"
  246.         startRedStr="exec DBMS_REDEFINITION.start_redef_table(uname => '"$user_name"',orig_table => '"$orig_table"',int_table => '"${int_table}"');"
  247.     elif [ ${option_flag} == "ROWID" ];then
  248.         canRedStr="exec dbms_redefinition.can_redef_table(""'"$user_name"'"",""'"$orig_table"',dbms_redefinition.cons_use_rowid);"
  249.         startRedStr="exec DBMS_REDEFINITION.start_redef_table(uname => '"$user_name"',orig_table => '"$orig_table"',int_table => '"${int_table}"',options_flag => dbms_redefinition.cons_use_rowid);"
  250.     else
  251.         echo "you must specify value for -f,only rowid/pk."
  252.         exit 8
  253.     fi
  254.     
  255.     if [ `CanRedef ${oraConStr} "${canRedStr}" | grep "successfully" | wc -l` -eq 0 ];then
  256.         echo ${orig_table}" can not redefine using "${option_flag}
  257.         exit 9
  258.     fi
  259.     ###################################################
  260.     #begin to redefinit table
  261.     ###################################################
  262.     redefinitionTable ${user_name} ${orig_table} ${int_table} "${oraConStr}" "${startRedStr}"
  263. elif [ ${action} == 'ABORT' ];then
  264.     AbortTable ${user_name} ${orig_table} ${int_table} "${oraConStr}"
  265. else
  266.     echo "The value of action only use [redefine|abort]."
  267.     exit 10
  268. fi



  272. ###################################################
  273. #core code
  274. ###################################################
  275. #exec dbms_redefinition.can_redef_table('$1', '$2');
  276. #exec DBMS_REDEFINITION.start_redef_table(uname => '$1',orig_table => '$2',int_table => '$3');
  277. #DECLARE
  278. #    num_errors PLS_INTEGER;
  279. #BEGIN
  280. #    DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS('$1', '$2','$3',
  281. #    DBMS_REDEFINITION.CONS_ORIG_PARAMS, TRUE, TRUE, TRUE, TRUE, num_errors);
  282. #END;
  283. #/
  284. #exec dbms_redefinition.sync_interim_table(uname => '$1',orig_table => '$2',int_table => '$3');
  285. #exec dbms_redefinition.finish_redef_table(uname => '$1',orig_table => '$2',int_table => '$3');











來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/28572479/viewspace-2151678/，如需轉載，請註明出處，否則將追究法律責任。

相關文章