oracle 备份与恢复1-数据库数据与文本文件

0.目录

 1.概述

 2.生成文本文件

  2.1 查询语句生成文本文件

  2.2 spool将数据库数据导出成文本文件

    2.2.1 语法

    2.2.2 设定sqlplus变量

    2.2.3 DIY导出数据模板

  2.3 windows环境下ociuldr工具生成文本文件

    2.3.1 ociuldr导出例子

    2.3.2keyword详解

 3.sqlldr将文本文件导入到数据库中

  3.1 概述

  3.2 例1简单

  3.3 例2需要转换数据类型

  3.4 例3需要跳过某些行

  3.5 例4多个txt

 4.表导出和导人实例

---------------------------------------------------------------------------------------

1. 概述

   oracle数据库中可以通过sql语句将表数据导出成文本文件，也可以通过spool将表导出成文本文件，也可通过ociuldr工具将表数据导出成文本文件；然后通过sqlldr将文本文件导出到已经建好的表中。常用于，生产数据同步到开发环境，或者表数据迁移。在导出文本文件时，尽量将日期

-----------------------------------------------------------------------------------------

2. 生成文本文件

2.1 查询语句生成文本文件

  scott>select EMPNO||'||ENAME||'||JOB||'||MGR||'||HIREDATE||'||SAL||'||COMM||'||DEPTNO     from emp;

--然后将查询结果粘贴到文本文件中。

2.2 spool将数据库数据导出成文本文件

2.2.1 语法

    scott>spool /tmp/emp.txt--打开spool，并指明生成txt文件的路径

       scott>select * from emp;--执行sql

       scott>spool off--关闭spool

  cd /tmp

  cat emp.txt 

  SCOTT>select * from emp;

  EMPNO ENAME  JOB    MGR HIREDATE   SAL COMM DEPTNO

  ---------- ---------- --------- ---------- --------- 

  7369 SMITH  CLERK   7902 17-DEC-80  800    20                       

  7499 ALLEN  SALESMAN 7698 20-FEB-81  1600 300 30   

  。。。。。。

  14 rows selected.

  SCOTT>spool off

  --可以看到第一个spool之后，到关闭spool之间所有的查询语句和查询结果都会显示到文本文件中。

  2.2.2 设定sqlplus的变量

  ***有时候我们想去美化或者格式化文本文件的内容，需要去设定sqlplus的变量

  scott>show all--查询当前会话sqlplus的变量

  可以直接输入set去限定当前会话的sqlplus变量

  如：默认的sqlplus登录后提示符为sql>,

  sql>set sqlprompt "_user>" 提示符就变为当前登录用户，如：scott>

  常见和常用的有：

   set echo off;--不显示脚本中正在执行的SQL语句

   set term off;--不将结果显示在屏幕上，直接导出到文件中

   set heading off;--不输出列名

   set feedback off; --默认行数大于6行会显示 xx rows selected.

   set linesize 100;--每行100个字符

   set pagesize 800;--每页800行，默认24

   set colsep |;--设定列之间的分隔符

   set verify off; --用于绑定变量是不显示old new信息

     SCOTT>select * from emp where EMPNO=7369;   

     EMPNO ENAME  JOB    MGR HIREDATE   SAL COMM DEPTNO

     ---------- ---------- --------- ---------- --------- 

     7369 SMITH  CLERK   7902 17-DEC-80  800    20   

     --绑定变量 

     SCOTT>select * from emp where EMPNO=&EMPNO;

     Enter value for EMPNO: 7369 

     old   1: select * from emp where EMPNO=&EMPNO

     new   1: select * from emp where EMPNO=7369 

     EMPNO ENAME  JOB    MGR HIREDATE   SAL COMM DEPTNO

     ---------- ---------- --------- ---------- --------- 

     7369 SMITH  CLERK   7902 17-DEC-80  800    20 

  2.2.3 导出数据模板

  cd /tmp

  vi spool.sql

   --20161222 for spool生成文本文件

   set echo off;

   set heading off;

   set feedback off;

   set pagesize 0;

   set colsep ,; 

   set verify off;

   spool &1;

   select * from &2;

   spool off 

 scott>@/tmp/spool.sql /tmp/emp.txt emp--导出成txt文件

 scott>@/tmp/spool.sql /tmp/emp.csv emp--导出成CSV文件

 2.3 windows环境下ociuldr工具生成文本文件（ociuldr.exe需自己下载）

   2.3.1 ociuldr导出例子

     ociuldr 不是内部工具，需要bat所在文件夹里面有ociuldr.exe，oracle客户端配置正常

     例1：

      步1：下载ociuldr.exe

      步2：新建文件，并重命名为out.bat，在out.bat中输入：

       ociuldr user=scott/tiger@orcl query="select * from emp" field=# head=YES batch=2          file="test_%%d.txt"

      （在命令行中直接输入也可）

      步3：双击out.bat

     注：bat文件和ociuldr一定要在同一文件夹

     例2：

       步1：下载ociuldr.exe

       步2：新建文件，并重命名为out.bat，在out.bat中输入：

       oociuldr.exe userscott/tiger@orcl sql=.\emp.sql  field="|"  

        file=.\emp.txt log=.\emp.log 

       步3：生成emp.sql文件，内容为查询的语句，不要有最后的分号

       步4：双击out.bat

      注：bat文件和ociuldr一定要在同一文件夹，sql文件不需要，只有保障路径正确

    2.3.2keyword详解

     User：指定目标数据库的用户名、密码和tnsname;

     Sql : 设置所要执行的sql文件；

     Query：设置所要执行的sql语句；

     Field：设置每个field间的分隔字符串；

     Record：设置每个record间的分隔字符串；

     Rows： 指定输出多少行以后打印一条日志，可以更好的观察进度（默认是1000000）

     File：设置导出的数据的文件名（如：sqluldrData.txt）；

     Log：设置生成的log文件；

     Text：设置输出的文件类型；

     Charset：设置输出的字符集；

     Ncharset: 设置输出的字符集；

     支持按照不同的批量导出数据，这通过一个参数batch来实现

     默认一个batch是50万条记录，如果不指定

     batch为2就表示100万条记录换一个文件 

     默认这个选项值是0，就是指不生成多个文件。

     field 分隔符

     head 是否打印标题，默认为NO

     可以尝试使用ociuldr导出的控制文件将数据加载到数据库中

-------------------------------------------------------------------------------------------

3.sqlldr 将文本文件导入到数据库中

 3.1 概述

 sqlldr为oracle自带的工具，用于将文本文件导入到数据库中。

[root@oracle@zsh bin]#cd /u01/app/oracle/product/11.2.0/dbhome_1/bin 

[root@oracle@zsh bin]# ll sql*

-rwxr-x--x 1 oracle oinstall 1362228 Nov 28 23:37 sqlldr

-rwxr-x--- 1 oracle oinstall       0 Aug 13  2009 sqlldrO

-rwxr-x--x 1 oracle oinstall    6889 Nov 28 23:37 sqlplus

  sqlldr命令很简单：sqlldr scott/tiger control=/tmp/emp2.ctl log=emp2.log

  sqlldr相关的文件：（1）文本文件，需要导入的数据

           （2）ctl控制文件,指明导入的文本，指明导入到的表信息，导入方式

              （3）log日志文件，记录导入过程 

              （4）错误数据文件.bad及失败文件.dsc(可以不指明)

  sqlldr的控制文件

  *.ctl格式为：

    load

    infile "d://test.txt"    外部数据文件

    infile "d://test1.txt"   可指定多个数据文件 (通过多行 infile 语句实现)

    append into table test   向表中追加数据

    fields terminated by ","    外部文件的数据以“，”分隔

    trailing nullcols        表中的字段没有对应的值时填充空值

    (

    id integer external,    integer external 表示插入的数据是string，如果只保留                          integer，表示插入的数据是二进制

    name "upper(:name)",     将插入的值转换为大写

    con ":id||:name",        表中CON列的值是ID和NAME的组合值

    dt date"yyyy-mm-dd"      插入日期型数据

    )

    在append的位置还可以用以下列表中的一个值：

    insert   向表中插入值，但要求表开始时为空

    replace  delete表中的数据，然后插入新值

    append   向表中追加数据

    truncate trunctate表，然后插入新值

    也可以按照字符的位置来导入数据

test.txt的数据如下

11,add,,2007-07-8

12,bd ,,2008-07-8

13,fcd,,2009-07-8

   （id position(1:2),

     name position(4:6),

     dt date"yyyy-mm-dd" position(9:17)--待验证

     ）

  3.2 例1简单

C:\Users\wzj>sqlldr scott/tiger control=d:/dept.ctl

dept.ctl的内容如下：

load

infile "d://test.txt"    

append into table tt

fields terminated by ","   

trailing nullcols       

(

id integer external,   

name "upper(:name)",     

con ":id||:name",       

dt date"yyyy-mm-dd"      

)

test.txt的数据如下

1,a,,2007-07-8

2,b,,2008-07-8

3,c,,2009-07-8

SQL> desc tt ;

名称   是否为空? 类型

----------------------

ID    VARCHAR2(10)                                     

NAME   VARCHAR2(20) 

CON    VARCHAR2(30) 

DT     DATE

  3.3 例2需要转换数据类型

ID CONSTANT "100"

DT "TRUNC(SYSDATE)"

  BEGTIME     date 'yyyy-mm-dd hh34:mi:ss' ,

  ENDTIME     date 'yyyy-mm-dd hh34:mi:ss' ,

  ETL_TIME     TIMESTAMP(6) 'yyyy-mm-dd hh34:mi:ss.ff6' ,

  3.4 例3需要跳过某些行

--一开始就跳 SKIP 5

payment.txt

BEG

1 || 1020100 || 02 || 20120828001 || CTIJ65002412000007 || 375409794 || 01 || 10000 ||

2 || 1020100 || 02 || 20120828001 || CTIJ65002412000006 || 375409794 || 01 || 10001 ||

3 || 1020100 || 02 || 20120828001 || CTIJ65002412000008 || 375409795 || 01 || 10002 ||

END

Load data

infile 'payment.txt' 

Append into table tmp_cx_autoagree_ids 

when (01) <> 'BEG' and (01) <> 'END'

--when (1:3)<>'BEG' AND (1:3)<>'END'

fields terminated by ' || '

trailing nullcols

  3.5 例4多个txt

--yugu_00000000000001.txt 结构如下：

保单号 |被保人×××|分公司|中支|险种|责任|责任子码|期次|频率|金额|渠道|是否生存金|是否预派发|来源系统|给付日期|

010111300053960|372928xxxxxx225626|00000000000001|00000000000103|28950100|200|00|1|2|804.0|31|1|1|APP0001|2015/01/21|

010111300053960|372928xxxxxxxx25626|00000000000001|00000000000103|28950100|200|00|2|2|804.0|31|1|1|APP0001|2016/01/21| 

--表bq_prepay_detail_yugu的结构如下：

YEARMTH                 VARCHAR2(10) Y 应付日期

ORGCODE1         VARCHAR2(24) Y 分公司

ORGCODE2         VARCHAR2(24) Y 中支

POLICYNO         VARCHAR2(20) Y 保单号

CLASSCODE         VARCHAR2(8) Y 险种

SALEATTR         VARCHAR2(10) Y 渠道

DELCODE                 VARCHAR2(3) Y 给付类型

DELNUM                 NUMBER(20) Y 期次

TYPENO                 VARCHAR2(2) Y 责任子码

GUEST_ID         VARCHAR2(100) Y 客户ID

JFTYPE                 VARCHAR2(20) Y

AMT                 NUMBER(20,2) Y 给付金额

ORGCODE3         VARCHAR2(24) Y 支公司

ISSCJ                 VARCHAR2(2) Y 是否生存金给付

ISPF                 VARCHAR2(2) Y 是否未来派发

--控制文件写法： bq_prepay_detail_yugu1446.ctl

load data

infile 'yugu_00000000000001.txt'

infile 'yugu_00000000000002.txt'

infile 'yugu_00000000000003.txt'

infile 'yugu_00000000000004.txt'

infile 'yugu_00000000000005.txt'

infile 'yugu_00000000000006.txt'

infile 'yugu_00000000000007.txt'

infile 'yugu_00000000000008.txt'

infile 'yugu_00000000000009.txt'

infile 'yugu_00000000000010.txt'

infile 'yugu_00000000000011.txt'

infile 'yugu_00000000000012.txt'

infile 'yugu_00000000000013.txt'

infile 'yugu_00000000000014.txt'

infile 'yugu_00000000000015.txt'

infile 'yugu_00000000000016.txt'

infile 'yugu_00000000000017.txt'

infile 'yugu_00000000000018.txt'

infile 'yugu_00000000000019.txt'

infile 'yugu_00000000000020.txt'

infile 'yugu_00000000000021.txt'

infile 'yugu_00000000000022.txt'

infile 'yugu_00000000000023.txt'

infile 'yugu_00000000000024.txt'

infile 'yugu_00000000000025.txt'

infile 'yugu_00000000000026.txt'

infile 'yugu_00000000000027.txt'

infile 'yugu_00000000000028.txt'

infile 'yugu_00000000000029.txt'

infile 'yugu_00000000000030.txt'

infile 'yugu_00000000000031.txt'

infile 'yugu_00000000000032.txt' 

truncate into TABLE bq_prepay_detail_yugu

Fields terminated by X'7c'

TRAILING NULLCOLS

(

POLICYNO,

GUEST_ID,

ORGCODE1,

ORGCODE2,

CLASSCODE,

DELCODE,

TYPENO,

DELNUM,

JFTYPE,

AMT,

SALEATTR,

ISSCJ,

ISPF,

orgcode3,

YEARMTH "replace (:YEARMTH,'/','')"

)

命令：

sqlldr bqtj/bqstatistics@FT CONTROL=bq_prepay_detail_yugu1446.ctl LOG=1446.LOG rows=1000 direct=y

4.表导出和导人实例

create table PAETL.C02_RELPAYRC

(

  SNO            INTEGER,

  TYPEID         INTEGER,

  CON_ID         INTEGER,

  POLICYNO       VARCHAR2(20),

  CLASSCODE      VARCHAR2(8),

  CHECK_ID       INTEGER,

  CHECKNO        VARCHAR2(40),

  AMT            NUMBER(20,4),

  CURRENCY       CHAR(3),

  GENDATE        DATE,

  DELFRM         CHAR(2),

  PAYCODE        CHAR(1),

  MOVEWHY        VARCHAR2(120),

  DELCODE        CHAR(3),

  TYPENO         CHAR(2),

  REGDATE        DATE,

  AGENT_ID       INTEGER,

  AGENTNO        VARCHAR2(20),

  OPER_ID        INTEGER,

  OPERNO         VARCHAR2(20),

  DEPTNO         VARCHAR2(20),

  DOCPRO         VARCHAR2(20),

  DOCNO          VARCHAR2(20),

  PERSON_ID      INTEGER,

  PID            VARCHAR2(40),

  IDTYPE         CHAR(2),

  PAYTIME        INTEGER,

  BEGTIME        DATE,

  ENDTIME        DATE,

  BRANCH         CHAR(14),

  ETL_TIME       TIMESTAMP(6),

  SRC_SYS        VARCHAR2(20),

  DEL_DATE       CHAR(8),

  REG_CODE       VARCHAR2(20),

  DEL_TYPE       INTEGER,

  SOUR_SYS       VARCHAR2(20),

  FGSNO          CHAR(3),

  EMPNO          CHAR(8),

  SECKEY         VARCHAR2(20),

  LOAD_TIME      TIMESTAMP(6),

  CONTNO         CHAR(8),

  SALE_MODE      CHAR(4),

  THE_THRDPRT    CHAR(4),

  CSRNO          VARCHAR2(20),

  JOB            CHAR(8),

  BUSI_BRANCH    CHAR(14),

  INSRNC_RATE    NUMBER(7,4),

  POLICY_NO_TYPE VARCHAR2(1),

  FIN_PROC_TYPE  INTEGER,

  SETNO          VARCHAR2(20)

)

--导成txt的bat

cd D:\wgw\Tool 

ociuldr.exe user=paetl/paetl0203@LIFEPF_ODB query=" SELECT * FROM  C02_RELPAYRC T WHERE T.REGDATE>=to_date('20141001','yyyymmdd')   " field="|"  file=.\C02_RELPAYRC.txt log=.\C02_RELPAYRC.log 

exit

/

sqlldr "paetl/*IK<9ol."@FT CONTROL=.\c02.CTL LOG=.\c02.LOG rows=30000 direct=y

c02.CTL内容：

load data

infile 'C02_RELPAYRC.txt' 

truncate into  TABLE "C02_RELPAYRC"

Fields terminated by "|" 

TRAILING NULLCOLS

( 

  SNO             ,

  TYPEID          ,

  CON_ID          ,

  POLICYNO        ,

  CLASSCODE       ,

  CHECK_ID        ,

  CHECKNO         ,

  AMT             ,

  CURRENCY        ,

  GENDATE         date 'yyyy-mm-dd hh34:mi:ss' ,

  DELFRM          ,

  PAYCODE         ,

  MOVEWHY         ,

  DELCODE         ,

  TYPENO          ,

  REGDATE         date 'yyyy-mm-dd hh34:mi:ss' ,

  AGENT_ID        ,

  AGENTNO         ,

  OPER_ID         ,

  OPERNO          ,

  DEPTNO          ,

  DOCPRO          ,

  DOCNO           ,

  PERSON_ID       ,

  PID             ,

  IDTYPE          ,

  PAYTIME         ,

  BEGTIME          date 'yyyy-mm-dd hh34:mi:ss' ,

  ENDTIME          date 'yyyy-mm-dd hh34:mi:ss' ,

  BRANCH          ,

  ETL_TIME        TIMESTAMP(6) 'yyyy-mm-dd hh34:mi:ss.ff6' ,

  SRC_SYS         ,

  DEL_DATE        ,

  REG_CODE        ,

  DEL_TYPE        ,

  SOUR_SYS        ,

  FGSNO           ,

  EMPNO           ,

  SECKEY          ,

  LOAD_TIME      TIMESTAMP(6) 'yyyy-mm-dd hh34:mi:ss.ff6' ,

  CONTNO         ,

  SALE_MODE       ,

  THE_THRDPRT     ,

  CSRNO           ,

  JOB             ,

  BUSI_BRANCH     ,

  INSRNC_RATE     ,

  POLICY_NO_TYPE  ,

  FIN_PROC_TYPE   ,

  SETNO           

)