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           

)