Import backups between mysql and oracle databases

  • 2021-11-24 03:12:46
  • OfStack

Import the data exported from the Oracle database into the MySql database

1. Import into the MySql database using the default end symbol:

LOAD DATA LOCAL INFILE 'd:/oracle.txt' IGNORE INTO TABLE alarmLog

The default terminator for Fields is the '/t' tab, not the '/t' string.

2. When the Fields terminator is set to import into the MySql database:

LOAD DATA LOCAL INFILE 'd:/oraclea.txt' IGNORE INTO TABLE alarmLog FIELDS TERMINATED BY '~'

In both cases, the data files oracle. txt and oraclea. txt are exported through the ociuldr. exe program.

Import data exported from MySql into oracle database

1. Importing data from MySql database to file by setting Fields terminator

select * from alarmLog into outfile 'd:/mysql.txt' FIELDS TERMINATED BY '~'

Import the d:/mysql. txt data file into the oracle database through sqlldr. Pay attention to the writing of the control file. The following is the corresponding control file for the test program.


OPTIONS(BINDSIZE=8388608,READSIZE=8388608,ERRORS=-1,ROWS=50000)
LOAD DATA
INFILE 'd:/mysql.txt' "STR X'0A'"
INTO TABLE instantdata
FIELDS TERMINATED BY '~' TRAILING NULLCOLS 
(
 FIELDID CHAR(20),
 CreateTIME date 'yyyy-mm-dd hh24:mi:ss',
 AckTIME date 'yyyy-mm-dd hh24:mi:ss',
 TYPE CHAR(40),
 QUALITY CHAR(40),
 VALUE CHAR(40),
 AVG CHAR(40),
 MAX CHAR(40),
 MIN CHAR(40),
 SUM CHAR(40)
)

Summarize


Related articles: