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           

)