#前置条件 ##源端 ######源数据是txt格式，类似如下： > "1","1","PC00001","2021-10-12 00:00:00","测试理财产品001","CNY","1.35","98.1",".45",".18","2.36","2.36","0","2.36","1","2021-10-12 00:00:00","2021-10-14 00:00:00.000000","2021-10-12 00:00:00.000000" #目标端  #迁移方案比较  根据上述4种方案，个人推荐方案一。 #shell方案 ** 1.脚本准备前** 1.1创建数据库对象 1.1.1创建IPRODUCT30对应表空间 ``` select * from dba_data_files; create tablespace IPRODUCT_DATA_DISK datafile '/data1/db1/IPRODUCT_DATA_DISK.dbf' size 20m;``` 1.1.2创建用户 ```create user IPRODUCT30 identified by IPRODUCT30 default tablespace IPRODUCT_DATA_DISK ;``` 1.1.3用户授权 ``` GRANT connect,resource,dba TO IPRODUCT30; grant CREATE TABLE, CREATE VIEW, CREATE SEQUENCE to IPRODUCT30; GRANT SELECT ANY TABLE TO IPRODUCT30; ``` 1.1.4创建表 ``` PRD_INCOME(产品净值收益表) CREATE TABLE IPRODUCT30.PRD_INCOME ( BANK_SEQ NUMBER(10) NOT NULL, INVEST_TYPE VARCHAR(2) NOT NULL, INVEST_CODE VARCHAR(32) NOT NULL, RELEASE_DATE DATE NOT NULL, INVEST_NAME VARCHAR(128), CURRENCY VARCHAR(3) DEFAULT 'CNY', INVEST_NAV NUMBER(20,6), INCOME NUMBER(20,2), INCOME_UNIT NUMBER(20,2), TOT_NAV NUMBER(20,6), INCOME_RATE NUMBER(20,6), ANNUALIZED_RATE NUMBER(20,6), ANNUALIZED_FLAG VARCHAR(2), MONTH_INCOME_RATE NUMBER(20,6), MONTH_FLAG VARCHAR(2), CREATE_DATE DATE, CREATE_TIME TIMESTAMP(6), UPDATE_TIME TIMESTAMP(6) ) ; COMMENT ON TABLE IPRODUCT30.PRD_INCOME IS '产品净值收益表'; COMMENT ON COLUMN IPRODUCT30.PRD_INCOME.BANK_SEQ IS '银行顺序号'; COMMENT ON COLUMN IPRODUCT30.PRD_INCOME.INVEST_TYPE IS '基金/理财产品类别 0-基金、1-行内理财、2-境外理财产品'; COMMENT ON COLUMN IPRODUCT30.PRD_INCOME.INVEST_CODE IS '基金/理财产品代码'; COMMENT ON COLUMN IPRODUCT30.PRD_INCOME.RELEASE_DATE IS '发布日期'; COMMENT ON COLUMN IPRODUCT30.PRD_INCOME.INVEST_NAME IS '基金/理财产品名称'; COMMENT ON COLUMN IPRODUCT30.PRD_INCOME.CURRENCY IS '币种 默认 CNY-人民币'; COMMENT ON COLUMN IPRODUCT30.PRD_INCOME.INVEST_NAV IS '基金/理财产品净值'; COMMENT ON COLUMN IPRODUCT30.PRD_INCOME.INCOME IS '基金/理财产品收益'; COMMENT ON COLUMN IPRODUCT30.PRD_INCOME.INCOME_UNIT IS '万份产品单位收益'; COMMENT ON COLUMN IPRODUCT30.PRD_INCOME.TOT_NAV IS '产品累计净值'; COMMENT ON COLUMN IPRODUCT30.PRD_INCOME.INCOME_RATE IS '产品收益率'; COMMENT ON COLUMN IPRODUCT30.PRD_INCOME.ANNUALIZED_RATE IS '七日年化收益'; COMMENT ON COLUMN IPRODUCT30.PRD_INCOME.ANNUALIZED_FLAG IS '七日年化收; ``` 1.1.5创建表主键和索引 ``` ALTER TABLE IPRODUCT30.PRD_INCOME ADD PRIMARY KEY (BANK_SEQ,INVEST_TYPE,INVEST_CODE,RELEASE_DATE); CREATE INDEX IDX_PRD_INCOME_1 ON IPRODUCT30.PRD_INCOME(INVEST_CODE) TABLESPACE IPRODUCT_TMP; CREATE INDEX IDX_PRD_INCOME_2 ON IPRODUCT30.PRD_INCOME(CREATE_DATE) TABLESPACE IPRODUCT_TMP;``` 1.2创建文件目录 1.2.1创建总目录 ```[oracle@gs08 ~]$ mkdir -p DATA_20211215 ``` 1.2.2创建schma目录 ``` [oracle@gs08 ~]$ cd DATA_20211215 [oracle@gs08 DATA_20211215]$ mkdir -p IPRODUCT30_20211215``` 1.2.3创建schema下的子目录 ``` [oracle@gs08 DATA_20211215]$ cd IPRODUCT30_20211215 [oracle@gs08 IPRODUCT30_20211215]$ mkdir -p control [oracle@gs08 IPRODUCT30_20211215]$ mkdir -p data [oracle@gs08 IPRODUCT30_20211215]$ mkdir -p export_log [oracle@gs08 IPRODUCT30_20211215]$ mkdir -p history [oracle@gs08 IPRODUCT30_20211215]$ mkdir -p import_log [oracle@gs08 IPRODUCT30_20211215]$ mkdir -p sh [oracle@gs08 IPRODUCT30_20211215]$ ll 总用量 24 drwxr-xr-x 2 oracle oinstall 4096 12月 15 16:24 control drwxr-xr-x 2 oracle oinstall 4096 12月 15 16:24 data drwxr-xr-x 2 oracle oinstall 4096 12月 15 16:24 export_log drwxr-xr-x 2 oracle oinstall 4096 12月 15 16:24 history drwxr-xr-x 2 oracle oinstall 4096 12月 15 16:24 import_log drwxr-xr-x 2 oracle oinstall 4096 12月 15 16:24 sh [oracle@gs08 IPRODUCT30_20211215]$ pwd /home/oracle/DATA_20211215/IPRODUCT30_20211215 ``` 1.2.4编辑控制文件 ``` [oracle@gs08 IPRODUCT30_20211215]$ cd control/ [oracle@gs08 control]$ vi IPRODUCT30.PRD_INCOME.ctl [oracle@gs08 IPRODUCT30_20211215]$ cd control/ [oracle@gs08 control]$ vi IPRODUCT30.PRD_INCOME.ctl [oracle@gs08 control]$ cat IPRODUCT30.PRD_INCOME.ctl [oracle@gs08 control]$ cat IPRODUCT30.PRD_INCOME.ctl``` ``` load data CHARACTERSET AL32UTF8 infile './../data/IPRODUCT30.PRD_INCOME.dat' append into table IPRODUCT30.PRD_INCOME fields terminated by ',' optionally enclosed by '"' ( bank_seq, invest_type, invest_code, release_date "to_date(:release_date,'YYYY-MM-DD HH24:MI:SS')", invest_name, currency, invest_nav, income, income_unit, tot_nav, income_rate, annualized_rate, annualized_flag, month_income_rate, month_flag, create_date "to_date(:create_date,'YYYY-MM-DD HH24:MI:SS')", create_time "to_timestamp(:create_time,'YYYY-MM-DD HH24:MI:SS.FF')", update_time "to_timestamp(:update_time,'YYYY-MM-DD HH24:MI:SS.FF')" ) ``` 1.2.5编辑表清单文件 ``` [oracle@gs08 control]$ cd ../history/ [oracle@gs08 history]$ vi TABLE_LIST.txt [oracle@gs08 history]$ cat TABLE_LIST.txt IPRODUCT30.PRD_INCOME``` 1.2.6编辑shell文件 ``` [oracle@gs08 history]$ cd ../sh/ [oracle@gs08 sh]$ vi import.sh [oracle@gs08 sh]$ cat import.sh ``` ``` SCHEMA=( `cat ../history/TABLE_LIST.txt | cut -d. -f1` ) TABLE=( `cat ../history/TABLE_LIST.txt | cut -d. -f2` ) CNT=${#TABLE[*]} if [[ "$TARGET_SCHEMA" == "" ]] then sqlldr IPRODUCT30/IPRODUCT30 control=./../control/IPRODUCT30.PRD_INCOME.ctl log=./../import_log/IPRODUCT30.PRD_INCOME.log bad=./../import_log/IPRODUCT30.PRD_INCOME.bad parallel=true else for((i=0;i<$CNT;i++)) do if [[ ${SCHEMA[i]} == ${TARGET_SCHEMA^^} ]] then sqlldr IPRODUCT30/IPRODUCT30 control=./../control/${SCHEMA[i]}.${TABLE[i]}.ctl log=./../export_log/${SCHEMA[i]}.${TABLE[i]}.log bad=./../export_log/${SCHEMA[i]}.${TABLE[i]}.bad fi done``` ** 2.调用执行shell** ``` [oracle@gs08 sh]$ chmod +x import.sh [oracle@gs08 sh]$ ll 总用量 8 -rwxr-xr-x 1 oracle oinstall 658 12月 15 16:44 import.sh -rw-r--r-- 1 oracle oinstall 73 12月 15 16:48 log [oracle@gs08 sh]$ nohup ./import.sh >/dev/null 2>log &**``` #3.验证结果 ```select t.invest_name from IPRODUCT30.PRD_INCOME t where rownum<10;``` #附录. > 1. 该方法可以作为Oracle定期的数据碎片清理方案，即：调用sqlldr导出数据txt，然后truncate表，然后sqlldr再导入txt数据（操作前先备份，export.sh脚本和导入脚本类似）。 > 2. 该方法可以改写成其他国产或开源数据库的数据迁移方案，比如mysql **dbtiger 2021年12月28日 于北京朝阳区永安里**