记录一下oracle两种常用的导入导出方式：exp、imp；spool、SQL*Loader及其脚本。
一、exp与imp导入导出
1、exp导出操作
exp c##sl/123456 buffer=64000 file=./full.sql full=y --导出整个数据库 exp c##sl/123456 buffer=64000 file=./sl.sql owner=c##sl --导出用户c##sl下的对象 exp c##sl/123456 buffer=64000 file=./book.sql tables=book --导出book表
exp c##sl/123456 buffer=64000 file=./book.sql tables=book,book2; --导出book、book2表
2、imp导入操作
exp c##sl/123456 buffer=64000 file=./book.sql tables=book drop table book; imp c##sl/123456 buffer=64000 file=./book.sql tables=book --需要先删除表，再导入，否则报错
exp c##sl/123456 buffer=64000 file=./book.sql tables=book
imp c##sl/123456 buffer=64000 ignore=y file=./book.sql tables=book --只会导入主键不冲突的数据，冲突的忽略
exp c##sl/123456 buffer=64000 file=./sl.sql owner=c##sl
imp c##sl/123456 buffer=64000 ignore=y file=./sl.sql full=y --导入sl.sql中的全部文件
imp c##sl/123456 buffer=64000 ignore=y file=./sl.sql tables=book,book2 --导入sl.sql中的表book、与book2
说明：tables指定导入或导出的表；full=y表示导入或导出全部；ignore=y表示跳过主键冲突执行
二、spool、SQL*Loader导入导出
准备表与数据
CREATE TABLE book( id varchar2(10) NOT NULL, name varchar2(50) DEFAULT NULL, author varchar2(20) DEFAULT NULL, price decimal(10,0) DEFAULT NULL, update_time date DEFAULT NULL, create_time date DEFAULT NULL, is_deleted varchar2(1) DEFAULT NULL, PRIMARY KEY (id) ); insert into book values ('1','c##','sl',23.4,to_date('2011-11-11 11:11:11','YYYY-MM-DD HH24:MI:SS'), to_date('2011-11-11 11:11:14','YYYY-MM-DD HH24:MI:SS'),1);
insert into book values ('2','c##','sl',23.4,to_date('2011-11-11 11:11:11','YYYY-MM-DD HH24:MI:SS'), to_date('2011-11-11 11:11:14','YYYY-MM-DD HH24:MI:SS'),1);
insert into book values ('3','c##','sl',23.4,to_date('2011-11-11 11:11:11','YYYY-MM-DD HH24:MI:SS'), to_date('2011-11-11 11:11:14','YYYY-MM-DD HH24:MI:SS'),1);
1、spool导出操作
创建sql文件book_spoolout.sql
set echo off set heading off set feedback off set pagesize 0 set linesize 1000 spool book.dat select id||','||name||','||author||','||price||','||to_char(update_time,'YYYY-MM-DD hh24:mi:ss')||','|| to_char(create_time,'YYYY-MM-DD hh24:mi:ss')||','||is_deleted from book; spool off
登录sqlplus，执行@导出文件
[root@localhost tmp]# sqlplus c##sl/123456
SQL> @book_spoolout.sql;
导出数据文件book.dat如下：
1,c##,sl,23,2011-11-11 11:11:11,2011-11-11 11:11:14,1 2,c##,sl,23,2011-11-11 11:11:11,2011-11-11 11:11:14,1 3,c##,sl,23,2011-11-11 11:11:11,2011-11-11 11:11:14,1
说明：
a、这里导出字段以逗号分隔；
b、这里登录数据库目录与sql文件、导出文件目录一致；
c、如果导出目录没有写权限，会报错“无法创建 SPOOL 文件 "book.dat"当前未假脱机”，注意赋权；
d、要把控制文件写入文件中保存，如果直接复制到sqlplus中执行，导出的文件中首尾行含有其他命令或sql语句；
2、SQL*Loader导入操作
创建控制文件book.ctl
load data infile book.dat into table book truncate fields terminated by "," ( ID, NAME, AUTHOR, PRICE, UPDATE_TIME DATE "YYYY-MM-DD HH24:MI:SS", CREATE_TIME DATE "YYYY-MM-DD HH24:MI:SS", IS_DELETED )
执行导入命令，将上面的book.dat导入数据库
sqlldr userid=c##sl/123456 control=book.ctl data=book.dat
注意：
a、这里的控制文件与数据文件在同一目录下
b、truncate是删除原表数据，还有insert、append、replace等
c、fields terminated by是字段分隔符
三、脚本
可以看到spool、sql loader的导入导出还是挺复杂的，下面整理出通用性更强的脚本
1、导出
准备表与数据
CREATE TABLE music( id varchar2(10) NOT NULL, name varchar2(50) DEFAULT NULL, author varchar2(20) DEFAULT NULL, price decimal(10,2) DEFAULT NULL, update_time varchar2(19) DEFAULT NULL, create_time varchar2(19) DEFAULT NULL, is_deleted varchar2(1) DEFAULT NULL, PRIMARY KEY (id) ); insert into music values ('1','你好你好','sl',23.4,'2011-11-11 11:11:11','2011-11-11 11:11:14','1'); insert into music values ('2','你好你好','sl',23.4,'2011-11-11 11:11:11','2011-11-11 11:11:14','1'); insert into music values ('3','你好你好','sl',23.4,'2011-11-11 11:11:11','2011-11-11 11:11:14','1');
创建脚本oddpe
#!/bin/sh if [ $# -lt 5 ];then echo "param error: please reference example:" echo "oddp music ./music.dat c##sl/123456 -f'|!?|' -r'@#$'" exit 1 fi TABLE=${1} DATAFILE=${2} USERPASS=${3} shift shift shift while getopts "f:r:" arg do case ${arg} in f) FIELD=${OPTARG};; r) ROW=${OPTARG};; ?) ROW=1;; esac done DATAPATH=`dirname ${DATAFILE}` DATANAME=`basename ${DATAFILE}` SPOOLOUTSQL=${DATAPATH}/${TABLE}_spoolout.sql TODAY=`date '+%Y%m%d'` LOGFILE=${DATAPATH}/${TABLE}_unload_${TODAY}.log sqlplus -S ${USERPASS} <<eof >>${LOGFILE} set serveroutput on verify off trimspool on timing off feedback off set numwidth 50 pagesize 0 linesize 1000 spool ${SPOOLOUTSQL} declare v_colcount number :=0; begin dbms_output.put_line('select'); for col in (select column_name from user_tab_columns where table_name = upper('${TABLE}') order by column_id) loop if v_colcount = 0 then dbms_output.put(chr(9) || col.column_name); else dbms_output.put_line(chr(9) || '||' || '''${FIELD}''' || '||' ); dbms_output.put(chr(9) || col.column_name); end if; v_colcount :=v_colcount + 1; end loop; dbms_output.put('||' || '''${ROW}'''); dbms_output.new_line; dbms_output.put_line('from ' || '${TABLE};'); end; / spool off exit eof echo "`date +%T`: 开始导出数据!" 2>&1|tee -a ${LOGFILE} sqlplus -S ${USERPASS} <<eof set echo off heading on feedback off pagesize 0 linesize 1000 set termout off trimspool on numwidth 24 arraysize 1000 verify off newpage 0 space 0 spool ${DATAFILE}.tmp @${SPOOLOUTSQL} spool off exit eof if [ $? -ne 0 ];then echo "`date +%T`: 导出${TABLE}失败!" 2>&1|tee -a ${LOGFILE} exit -1 else #去除中间空行 grep . ${DATAFILE}.tmp>${DATAFILE} rm -f ${DATAFILE}.tmp echo "`date +%T`: 导出${TABLE}完毕!" 2>&1|tee -a ${LOGFILE} exit 0 fi
执行示例，导出music表的数据到music.dat文件，以|!?|分隔字段，以@#$结束一行
./oddpe music /usr/local/myroom/script/tmp/music.dat c##sl/123456 -f'|!?|' -r'@#$'
2、导入
创建脚本oddpi
#!/bin/sh if [ $# -lt 5 ];then echo "param error: please reference example:" echo "oddpi music ./music.dat c##sl/123456 -f'|!?|' -r'@#$'" exit 1 fi TABLE=${1} DATAFILE=${2} USERPASS=${3} shift shift shift while getopts "f:r:" arg do case ${arg} in f) FIELD=${OPTARG};; r) ROW=${OPTARG};; ?) ROW=1;; esac done DATAPATH=`dirname ${DATAFILE}` DATANAME=`basename ${DATAFILE}` CTLFILE=${DATAPATH}/${TABLE}.ctl TODAY=`date '+%Y%m%d'` LOGFILE=${DATAPATH}/${TABLE}_load_${TODAY}.log echo "`date +%T`: 创建控制文件" 2>&1|tee -a ${LOGFILE} sqlplus -S ${USERPASS} <<eof >>${LOGFILE} set serveroutput on verify off trimspool on timing off feedback off set numwidth 50 spool ${CTLFILE} declare v_colcount number :=0; begin dbms_output.put_line('load data'); dbms_output.put_line('infile ${DATANAME} '|| '"str ''${ROW} ''" '); dbms_output.put_line('into table '||'${TABLE}'); dbms_output.put_line('truncate'); dbms_output.put_line('fields terminated by '|| '''${FIELD}'''); dbms_output.put_line('trailing nullcols'); dbms_output.put_line('('); for col in (select column_name, case when data_type = 'NUMBER' then column_name || ' "nvl(rtrim(:' || column_name || '),' || '0.00' || ')"' else column_name || ' "nvl(rtrim(:' || column_name || '),' || ''' ''' || ')"' end x from user_tab_columns where table_name = upper('${TABLE}') order by column_id) loop if v_colcount = 0 then dbms_output.put(chr(9) || col.x); else dbms_output.put_line(','); dbms_output.put(chr(9) || col.x); end if; v_colcount :=v_colcount + 1; end loop; dbms_output.new_line; dbms_output.put_line(')'); end; / spool off set feedback on eof echo "`date +%T`: 开始导入数据!" 2>&1|tee -a ${LOGFILE} sqlldr userid=${USERPASS} control=${CTLFILE} log=${LOGFILE} data=${DATAFILE} direct=true if [ $? -ne 0 ];then echo "`date +%T`: 导入${TABLE}失败!" 2>&1|tee -a ${LOGFILE} exit -1 else echo "`date +%T`: 导入${TABLE}完毕!" 2>&1|tee -a ${LOGFILE} exit 0 fi
执行脚本，导入数据
./oddpi music /usr/local/myroom/script/tmp/music.dat c##sl/123456 -f'|!?|' -r'@#$'
说明：对导入脚本中dbms_output.put_line('infile ${DATANAME} '|| '"str ''${ROW} ''" ');的解析
load data的str属性表示数据的换行符，比如
load data infile music.dat "str '!! '" into table ... ... 表示数据文件中以!!换行，如下：
3^^你好你好^^sl^^23.4^^2011-11-11 11:11:11^^2011-11-11 11:11:14^^1!! 2^^你好你好^^sl^^23.4^^2011-11-11 11:11:11^^2011-11-11 11:11:14^^1!! 1^^你好你好^^sl^^23.4^^2011-11-11 11:11:11^^2011-11-11 11:11:14^^1!!
a、" "是自带的换行符，因为数据手动换行了，所以str后面除了指定的换行符"!!"还有" "
b、有些文件中手动的换行符不是" "而是" "，这时对应语句改为......"str '!! '"......
c、str后面还可以跟X+转成raw类型的字符，下面的语句执行效果相同。
dbms_output.put_line('infile book.dat '|| '"str ''@#$
''" ');
dbms_output.put_line('infile book.dat '|| '"str X''4023240A''" ');
select utl_raw.cast_to_raw('@#$') from dual查询结果是402324，0A代表换行。（查询结果也可能原样输出，跟数据库字符集有关）