MySQL 迁移 oralce

使用sqldeveloper进行数据库库迁移

版本：19.1.0.094

引入第三方MySQL连接

jar包可以到官网上找链接, mysql_conntecter_java版本是5.1.47

创建oracle和MySQL连接

1. oracle创建用户需要与迁移的MySQL数据库一致
2. 给创建的用户赋权限。 需要创建table

create user mts identified by mts default tablespace users;
alter user mts quota unlimited on users;
 
grant connect to mts;
grant resource to mts;

grant alter any role to mts;  
 
grant alter any sequence to mts;  
 
grant alter any table to mts;  
 
grant alter any trigger to mts;  
 
grant alter tablespace to mts;  
 
grant comment any table to mts;  
 
grant create any sequence to mts;  
 
grant create any table to mts;  
 
grant create any trigger to mts;  
 
grant create any view to mts;  
 
grant create materialized view to mts with admin option;  
 
grant create public synonym to mts with admin option;  
 
grant create role to mts;  
 
grant create session to mts with admin option;  
 
grant create synonym to mts with admin option;  
 
grant create tablespace to mts;  
 
grant create user to mts;  
 
grant create view to mts with admin option;  
 
grant drop any role to mts;  
 
grant drop any sequence to mts;  
 
grant drop any table to mts;  
 
grant drop any trigger to mts;  
 
grant drop tablespace to mts;  
 
grant drop user to mts;  
 
grant grant any role to mts;  
 
grant insert any table to mts;  
 
grant select any table to mts;  
 
grant unlimited tablespace to mts with admin option;  
 
grant update any table to mts; 

迁移

依次向下执行即可。

需要删除迁移产生的中间表各种MD表等

由于是把历史数据转移到新库中，所以需要把上面mts用户的数据，转移到自己指定的用户。

案例中是把mts表数据，转移到kondor表数据。

给kondor授权，可以查看mts表数据

-- 授权查询另一个用户的表
--1、 查询执行语句
select 'grant select on MTS.'||table_name||' to kondor;' from all_tab_comments where OWNER = 'MTS';
-- 将上方查询结果，执行一遍
grant select on MTS.WTRADE_REPODEALS_HIS to kondor;
grant select on MTS.WTRADE_REPODEALS to kondor;
grant select on MTS.WTRADE_LEND to kondor;
grant select on MTS.WTRADE_LDREPODEALS_HIS to kondor;
grant select on MTS.WTRADE_LDREPODEALS to kondor;
grant select on MTS.WTRADE_IAMDEALS_HIS to kondor;
grant select on MTS.WTRADE_IAMDEALS to kondor;
grant select on MTS.WTRADE_BONDDEALS_HIS to kondor;
grant select on MTS.WTRADE_BONDDEALS_COPY to kondor;
grant select on MTS.WTRADE_BONDDEALS_1219 to kondor;
grant select on MTS.WTRADE_BONDDEALS to kondor;

grant select on MTS.TEST to kondor;
grant select on MTS.TCMS_DATA_INFO to kondor;
grant select on MTS.SECURITY_PORTFOLIO to kondor;
grant select on MTS.SECURITY_PFOLIO_TD to kondor;
grant select on MTS.SECURITY_OPTION_INFO to kondor;
grant select on MTS.REPO_PFOLIO_TD to kondor;
grant select on MTS.PLUP_INFO1 to kondor;
grant select on MTS.PLUP_INFO to kondor;
grant select on MTS.OPEN_REPO_PFOLIO_TD to kondor;
grant select on MTS.MTR_REPODEALS_HIS to kondor;
grant select on MTS.MTR_REPODEALS_BACKUP_HIS to kondor;

grant select on MTS.MTR_OPENREPODEALS_HIS to kondor;
grant select on MTS.MTR_OPENREPODEALS_BACKUP_HIS to kondor;
grant select on MTS.MTR_IBODEALS_HIS to kondor;
grant select on MTS.MTR_IBODEALS_BACKUP_HIS to kondor;
grant select on MTS.MTR_CDCPRICE_HIS to kondor;
grant select on MTS.MTR_CDCPRICE_BACKUP_HIS to kondor;
grant select on MTS.MTR_BONDSINFO_20180820 to kondor;
grant select on MTS.MTR_BONDSINFO_1219 to kondor;
grant select on MTS.MTR_BONDSDEALS_HIS2 to kondor;
grant select on MTS.MTR_BONDSDEALS_HIS to kondor;
grant select on MTS.MTR_BONDSDEALS_BACKUP_HIS to kondor;

grant select on MTS.MTR_BONDSDEALS_20161026 to kondor;
grant select on MTS.MTM_FOLDERS to kondor;
grant select on MTS.MTM_FLOATINGRATESIRS to kondor;
grant select on MTS.MTM_FLOATINGRATES to kondor;
grant select on MTS.MTM_CPTYS_BAK to kondor;
grant select on MTS.MTM_CPTYS to kondor;
grant select on MTS.MTM_CLEARINGMODES to kondor;
grant select on MTS.MTM_BONDS_HIS to kondor;
grant select on MTS.MTM_BONDS to kondor;
grant select on MTS.LDREPODEAL_INFOS to kondor;
grant select on MTS.IMIX_DEALS_INFO to kondor;

grant select on MTS.IB_LOAN_PFOLIO_POS to kondor;
grant select on MTS.FLOATINGBOND_FCR to kondor;
grant select on MTS.DEL_MTR_BONDSDEALS_1031 to kondor;
grant select on MTS.DEL_MTR_BONDSDEALS1031 to kondor;
grant select on MTS.CDC_SUCC_INFO to kondor;
grant select on MTS.CDC_PRICE to kondor;
grant select on MTS.CDC_FP to kondor;
grant select on MTS.CDC_DEVATION_SETTING to kondor;
grant select on MTS.BONDS_BASE_INFO_COPY to kondor;
grant select on MTS.BONDS_BASE_INFO to kondor;
grant select on MTS.ASPCLIENT to kondor;