Oracle Data Import and Export

Recently, I learned how to import and export ORACLE data. Many friends say that exp and IMP are used for import and export. However, exp and IMP are not flexible. The format of the generated file is not well controlled when exporting single table data. Some ETL systems extract and load data. For Oracle, data is exported through sqlplus spool, and sqlldr loads data.

The following two scripts are written in shell to export and load ORACLE data:

Export: Use sqlplus spool, but there are many set commands. Copy them online !, I encountered a problem during the test: When executing spool export on the command line, I will export the spool redundant information to the data file together, but put it in the shell script, is a pure data file.

#!/bin/bash#sqlplus -S /nolog > result.log<<EOF#conn mymis/mymissqlplus -S /nolog > result.log<<EOFconn mymis/mymis@127.0.0.1:1521/ORCLset head offset headsep offset newp noneset linesize 100set pagesize 10000set sqlblanklines OFFset trimspool ONset termout offset feedback offspool d:\export.datSELECT USER_ID || ',' || USER_NAME || ',' || USER_PASSWORD || ',' || USER_MAIL || ',' || PHONE_NO || ',' || REMARK FROM MYMIS.TB_MYMIS_USER_INFO;spool offexitEOF

Import: sqlldr is used for loading. You need to configure a control file! Shell can read the control file.

The following control file:

LOAD DATAINFILE 'd:\export.dat'badfile 'l2.bad'Append INTO TABLE MYMIS.TB_MYMIS_USER_INFO_BAKFIELDS TERMINATED BY ','TRAILING(  USER_ID,  USER_NAME,  USER_PASSWORD,  USER_MAIL,  PHONE_NO,  REMARK)

The following is a shell script:

#!/bin/bashim_data(){  sqlldr mymis/mymis@127.0.0.1:1521/ORCL log=l2.log control=l2.ctl streamsize=25600000    echo "sqlldr1 end"  exit  EOF}im_data

This completes the data import and export!

Digress: Unix shell is learned in windows. cygwin is a good thing and can be recommended to you!