oracle到kdb迁移,利用sqlldr迁移数据- KDB到Oracle

利用sqlldr迁移数据- KDB到Oracle

发布时间：2020-08-08 06:34:08

来源：ITPUB博客

阅读：108

作者：fwinmachao

栏目：数据库

./ 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;

/