oracle19c之导入、导出及脚本

　　记录一下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代表换行。（查询结果也可能原样输出，跟数据库字符集有关）