利用sqlldr迁移数据-KDB到Oracle

./ kdb_to_oracle.sh user_name user_password
eg: ./get_ddl.sh nda_202 NDA_202  --从kdb导出nda_202 下的所有对象通过sqlldr加载到oracle数据库中

kdb_to_oracle.sh 脚本:
#!/bin/bash

user_name=${1}
user_pass=${2}
schema_name=${3}

if [ ! -d log ]
then
mkdir log
fi

if [ ! -d ctl_file ]
then
mkdir ctl_file
fi

if [ ! -d sql ]
then
mkdir sql
fi

if [ ! -d data ]
then
mkdir data
fi

#get object type and name, T1:TABLE I1:INDEX
kdsql ${user_name}/${user_pass} << + >tab.txt.tmp
set pagesize 0
select 'TAB_NAME% '||object_name||':'||object_type from user_objects;
+

egrep TAB_NAME tab.txt.tmp |awk -F'%' '{print $NF}' > tab.txt
rm -rf tab.txt.tmp

#get ddl
for i in `cat tab.txt`
do
obj_type=`echo ${i}|awk -F ':' '{print $2}'`
obj_name=`echo ${i}|awk -F ':' '{print $1}'`
kdsql ${user_name}/${user_pass} << + >>tab.ddl
set pagesize 0
set long 999999
set linesize 20000
set heading off;
set feedback off;
spool table_ddl.txt append
SELECT DBMS_METADATA.GET_DDL('${obj_type}','${obj_name}') from dual;
spool off
+
done

sed -i '/machao>/d' table_ddl.txt
sed -i '/SQL>/d' table_ddl.txt

#get control file for sqlldr from oracle database
for i in `cat tab.txt |grep TABLE|awk -F':' '{print $1}'`
do
sqlplus -s ${user_name}/${user_pass} set heading off                         
set serveroutput on
set linesize 1000
exec P_generate_sqlldr_null('${i}');
EOF
done
sed -i '/PL\/SQL procedure successfully completed/d' /app/metadata/ctl_file/*.ctl

#generate sql to export plain text from kdsql

for i in `ls ctl_file/`
do
file_name=`echo ${i}|awk -F'.' '{print $1}'`
sed -n '/TRAILING NULLCOLS/,$p' ctl_file/$i > sql/${file_name}.sql
sed -i "s/TRAILING NULLCOLS (/select/g" sql/${file_name}.sql
sed -i 's/timestamp "yyyy-mm-dd hh34:mi:ss"//g' sql/${file_name}.sql
sed -i "s/^,/||','||/g" sql/${file_name}.sql
sed -i "s/)/ from ${file_name};/g" sql/${file_name}.sql
done

#export data to *.txt from inspure database

for i in `cat tab.txt |grep TABLE|awk -F':' '{print $1}'`
do
file_name=`echo $i|awk -F'.' '{print $1}'`
kdsql ${user_name}/${user_pass} << + >/dev/null
set colsep ',';
set echo off;
set feedback off;
set heading off;
set pagesize 0;
set termout off;
set trimout on;
set trimspool on;
set linesize 30000;
spool data/${i}.txt
@sql/${i}.sql
spool off
+
done

sed -i 's/machao>//g' data/*.txt
sed -i 's/spool//g' data/*.txt
sed -i '/spool off/d' data/*.txt
sed -i '/@sql\//d' data/*.txt

#load plain text to oracle database with sqlldr
#sqlldr ${user_name}/${user_pass} control=ctl_file/DA_MD_503506.ctl data=data/DA_MD_503506.txt log=log/DA_MD_503506.log

for i in `cat tab.txt |grep TABLE|awk -F':' '{print $1}'`
do
sqlldr ${user_name}/${user_pass} control=ctl_file/${i}.ctl data=data/${i}.txt log=log/${i}.log >/dev/null
done

#timestamp "yyyy-mm-dd hh34:mi:ss"


#SP of P_generate_sqlldr_null
CREATE OR REPLACE PROCEDURE P_GENERATE_SQLLDR_NULL (p_table_name IN VARCHAR2) AS
l_curr_line LONG;
l_table_name user_tables.table_name%TYPE;
BEGIN
select table_name
into l_table_name
from user_tables
where table_name =upper(p_table_name);
l_curr_line := '
LOAD DATA
INFILE '''||upper(l_table_name)||'.txt''
INTO TABLE '||upper(l_table_name)||chr(10)||
'FIELDS TERMINATED BY '','''||chr(10)||'TRAILING NULLCOLS (';

for rec in ( select table_name,column_name,column_id,data_type
from user_tab_columns
where table_name =upper(p_table_name)
order by column_id) loop
if rec.column_id = 1 THEN
IF rec.data_type = 'DATE' or rec.data_type='TIMESTAMP(6)' THEN
l_curr_line := l_curr_line||'
'||rec.column_name||' '||'timestamp "yyyy-mm-dd hh34:mi:ss"';
ELSE
l_curr_line := l_curr_line||'
'||rec.column_name;
END IF;
ELSE
IF rec.data_type = 'DATE' or rec.data_type='TIMESTAMP(6)' THEN
l_curr_line := l_curr_line||'
,'||rec.column_name||' '||'timestamp "yyyy-mm-dd hh34:mi:ss"';
ELSE
l_curr_line := l_curr_line||'
,'||rec.column_name;
END IF;

end if;

end loop;
l_curr_line := l_curr_line||')';
dbms_output.put_line(l_curr_line);
END P_generate_sqlldr_null;
/