利用sqlldr迁移数据- KDB到Oracle
发表于:2024-09-22 作者:千家信息网编辑
千家信息网最后更新 2024年09月22日,./ kdb_to_oracle.sh user_name user_passwordeg: ./get_ddl.sh nda_202 NDA_202 --从kdb导出nda_202 下的所有对象通过
千家信息网最后更新 2024年09月22日利用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;
/
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;
/
数据
对象
数据库
脚本
数据库的安全要保护哪些东西
数据库安全各自的含义是什么
生产安全数据库录入
数据库的安全性及管理
数据库安全策略包含哪些
海淀数据库安全审计系统
建立农村房屋安全信息数据库
易用的数据库客户端支持安全管理
连接数据库失败ssl安全错误
数据库的锁怎样保障安全
磁县企业万词霸屏需要服务器吗
吉林有名的网络技术服务口碑推荐
vb本地数据库查询
进入sql数据库后台方法
周杰伦铃声软件开发
酒店管理服务器配置
国家级二级数据库
计算机网络技术专科专业介绍
重庆浪潮服务器维修
中国网络安全管理报告
网络技术自学书籍
网络安全员法制竞赛
镇江多功能软件开发行业
网络安全与网络安全法有感
现在哪个服务器联盟小号多
如何取消网络安全管理
网络安全数通
服务器安全链接建立失败
深圳鑫诚软件开发
mac http服务器
ios软件开发基础知识
服务器主板cnas检测报告
网络安全工作 简报
全新服务器购买合同
网络安全战略心得体会
部队有网络安全科吗
神奇宝贝服务器怎么注册多个账号
数据库技术实验报告总结
三种常用的网络安全技术
汝城欣欣向荣网络技术服务中心