Summary of ORACLE migration to MYSQL

During the past two weeks, I have been busy with the migration of databases. When I did not do it, I felt it was very easy, but I was dumpfounded when I did it. In this tangle, let's talk about the problem:
1. Database Table Structure Problem: different data types need to solve varchar2 ------ varchar, number ----- int, date ---- datetime, how to solve the default value and comment of the SQL statement field for table creation. 2. There are no sensitive fields in oracle, but there are many sensitive fields in mysql table. It was strange that I did not know where the error was. The original describe field is a mysql sensitive field. Here I also found a ready-made tool on the Internet: oracletomysql, which can only migrate the table structure for us. Specific address: http://www.5stardatabasesoftware.com/cn/ www.2cto.com 3. oracle backup SQL can not run in normal mysql, some to_date () function, to_char () is very painful don't know how to replace,

The reason is simple: there is a pile of explanatory text in the oracle backup file: [html] prompt PL/SQL Developer import file prompt Created on 2012-05-30 by chenbh set feedback off set define off prompt Disabling triggers for T_ B _AUDITOR... alter table T_ B _AUDITOR disable all triggers; prompt Loading T_ B _AUDITOR... insert into T_ B _AUDITOR (AUDITORID, NAME, ORGID, SEX, IDCARDNO, TITLE, PHONE, MOBILE, DESCRIBE, AUDITORRIGHT, AUDITORSTATUS, RECORDSTATUS, FIELD1, FIELD2)
How can we remove these things? The idea may be that I directly run them in mysql after they are deleted, but if you think that your backup file is too large, you just cannot open it. I have encountered 1 GB of SQL backup, but I cannot open my computer. I have no choice but to think about other methods. Thanks here: Friends of the ITPUB forum of philip_zhong, http://www.itpub.net/forum.php? Mod = viewthread & tid = 1453387 & highlight = oracle2mysql
Here he provides a program to handle the migration of large data volumes. Here I will talk about how to use it. He provides a variety of methods, such as shell scripts, bat startup in windows, and source programs. I tried it. The first two were not called, so I had to execute his source program with my scalp. After debugging, I finally called it. I'm glad to know that ......
Please note that: www.2cto.com [html] static datasync1_ceparameter parameter ceparameters; static dataSyncSessionParameter sessionParameter; // static final String configFileName = "config. properties "; // here is the parameter in the source program. Configure static final String configFileName =" config_oracle2mysql.properties "as needed "; // here is my configuration file/*** @ param args */public static void main (String [] args) {// initialize the parameters // Strin G progPath = args [0]; // String progPath = "D: // work // MyEclipse 8.5 // Workspaces // dataSync"; E: // workspace // oracletomysql // package String progPath = "E: // workspace // oracletomysql // package"; // note the following, is the location of your package. String confFilePath = progPath + "// conf"; if (setparameters (confFilePath) {// start to call thread to sync the data syncData (); www.2cto.com} config_oracle2mysql.properties configuration file: it should be noted that ora_hash is a 10 Gb function. Here we will change it from the following: DBMS_UTILITY.GET_HASH_VALUE. This hash function is a function similar to ora_hash that has been found online for a long time. My understanding here is to save time for multi-threaded large data migration, and the author uses the hash method to control it. Other notes I have written in the program to remind me of the second and remind everyone not to make my mistake and waste everyone's time. [Html] # for source database parameters source. dataSource. initialSize = 10 source. dataSource. maxIdle = 20 source. dataSource. minIdle = 5 source. dataSource. maxActive = 100 source. dataSource. maxwait= 120000 source. jdbc. driverClassName = oracle. jdbc. driver. oracleDriver source. jdbc. url = jdbc: oracle: thin: @ 10.17.199.8: 1521: lab1107 source. jdbc. username = lab1107 source. jdbc. password = lab1107 # Target sync data threadNum = source. database. threadNum source. database. threadNum = 10
# Here, the auditorid must be the primary key and the ora_hash must be used in 10 Gb. Our 9i cannot be used. Source. database. selectSql = select * from t_ B _role where DBMS_UTILITY.GET_HASH_VALUE (roleid, 1, # threadNum #) =? # You can input commands and split by ";" source. database. sessionCommand = alter session set DB_FILE_MULTIBLOCK_READ_COUNT = 128; www.2cto.com # for target jdbc parameters target. dataSource. initialSize = 10 target. dataSource. maxIdle = 20 target. dataSource. minIdle = 5 target. dataSource. maxActive = 100 target. dataSource. maxwait= 120000 target. jdbc. driverClassName = com. mysql. jdbc. driver target. jdbc. url = jdbc: mys Ql: // 10.5.110.239: 3306/test? AutoReconnect = true & characterEncoding = UTF-8 target. jdbc. username = root target. jdbc. password = chen # target. database. insertSql = insert into test2 (PATHALIASID, PATH, CREATETIME, LASTMODIFIEDTIME, OBJECTPREFIX, PATHMD5ID, COLLIDESWITH) values (?,?,?,?,?,?,?) Target. database. insertSql = insert into T_ B _ROLE (ROLEID, ROLENAME, ROLEDESC, ROLESTATUS, RECORDSTATUS, FIELD1, FIELD2, SORTNUM) values (?,?,?,?,?,?,?,?) This must be a ready-made table in the target database. Target. database. commitNum = 1000 if you have encountered specific problems, you can talk about them together. Author: chen861201