#前置条件
##源端
######源数据是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"

#目标端
![目标端版本信息](http://img.blog.itpub.net/blog/2021/12/28/a68a630c05f061b7.png?x-oss-process=style/bb "目标端版本信息")

#迁移方案比较
![迁移工具比较](http://img.blog.itpub.net/blog/2021/12/28/2848d6cb6bada819.png?x-oss-process=style/bb "迁移工具比较")

根据上述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日 于北京朝阳区永安里**

ITPub博客

数据迁移

最新文章