shell 数据库建表语句脚本

#!/bin/sh
SHELL_NAME=GetTabStript.sh
/bin/rm -f CreateTable.sql
/bin/rm -f tmp.sql

##获取数据库用户名、密码、实例，属主，表名称列表信息；执行格式如下：
##指定表：sh GetTabStript.sh  "dbrwdadm" "dbrwdadm" "chnrwd" "DBRWDADM" "DCUSTMSG SCHNKPI" "CreateTable.sql"
##所有表：sh GetTabStript.sh  "dbrwdadm" "dbrwdadm" "chnrwd" "DBRWDADM" "" "CreateTable.sql"
}
v_ower=${4}
v_tablist=${5}
v_tabscript=${6}

##数据库连接，查询建表、索引语句
fExpTab()
{
sqlplus -s ${v_dbstr} <set serveroutput on
set linesize 2000
set long 99999
set pagesize 0
set feedback off
execute dbms_metadata.set_transform_param(dbms_metadata.session_transform,'STORAGE',false);
@./${1}
exit
eof
}

##判断v_tablist 是否为空，进而调用不同查询语句  
if [ -n "${v_tablist}" ];
then
 ##查询指定表建表语句
 for v_tab in $v_tablist
 do
  v_sqlcon="
   select dbms_metadata.get_ddl(U.OBJECT_TYPE,u.object_name)  \"/\"
     from USER_OBJECTS U
    where USER = upper('${v_ower}')
      and U.OBJECT_TYPE IN('INDEX','TABLE')
      and u.object_name in upper('${v_tab}');"
     
  ##将查询建表语句输出到临时sql脚本       
  echo "${v_sqlcon}">>tmp.sql  
  ##fExpTab "${v_sqlcon}">>tabscript.tmp
 done
else
 ##查询用户下所有表建表语句
 v_sqlall="
  select dbms_metadata.get_ddl(U.OBJECT_TYPE,u.object_name)  \" \"
    from USER_OBJECTS U
   where USER = upper('${v_ower}')
     and U.OBJECT_TYPE IN('INDEX','TABLE')
   order by U.OBJECT_TYPE desc;"
  
 ##将查询建表语句输出到临时sql脚本 
 echo "${v_sqlall}">>tmp.sql
 ##fExpTab "${v_sqlall}">>tabscript.tmp
fi

##调用数据库函数执行临时sql脚本，并生成临时建表脚本
fExpTab "tmp.sql">>tabscript.tmp

##读取并规范临时脚本文件
##将临时脚本中的多余空行，替换为"/",使之成为能直接被数据库调用的建表脚本
awk -v RS='\r\n' 'gsub(/\n\n\n/,"\n/")' tabscript.tmp >>${v_tabscript}

rm -f  tabscript.tmp tmp.sql