oracle 備份與恢復(fù)1-數(shù)據(jù)庫(kù)數(shù)據(jù)與文本文件

發(fā)布時(shí)間：2020-06-12 12:37:42 來(lái)源：網(wǎng)絡(luò) 閱讀：706 作者：Tqy_zsh 欄目：數(shù)據(jù)庫(kù)

0.目錄

1.概述

2.生成文本文件

2.1 查詢語(yǔ)句生成文本文件

2.2 spool將數(shù)據(jù)庫(kù)數(shù)據(jù)導(dǎo)出成文本文件

2.2.1 語(yǔ)法

2.2.2 設(shè)定sqlplus變量

2.2.3 DIY導(dǎo)出數(shù)據(jù)模板

2.3 windows環(huán)境下ociuldr工具生成文本文件

2.3.1 ociuldr導(dǎo)出例子

2.3.2keyword詳解

3.sqlldr將文本文件導(dǎo)入到數(shù)據(jù)庫(kù)中

3.1 概述

3.2 例1簡(jiǎn)單

3.3 例2需要轉(zhuǎn)換數(shù)據(jù)類型

3.4 例3需要跳過某些行

3.5 例4多個(gè)txt

4.表導(dǎo)出和導(dǎo)人實(shí)例

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

概述
oracle數(shù)據(jù)庫(kù)中可以通過sql語(yǔ)句將表數(shù)據(jù)導(dǎo)出成文本文件，也可以通過spool將表導(dǎo)出成文本文件，也可通過ociuldr工具將表數(shù)據(jù)導(dǎo)出成文本文件；然后通過sqlldr將文本文件導(dǎo)出到已經(jīng)建好的表中。常用于，生產(chǎn)數(shù)據(jù)同步到開發(fā)環(huán)境，或者表數(shù)據(jù)遷移。在導(dǎo)出文本文件時(shí)，盡量將日期

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

2. 生成文本文件

2.1 查詢語(yǔ)句生成文本文件

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

--然后將查詢結(jié)果粘貼到文本文件中。

2.2 spool將數(shù)據(jù)庫(kù)數(shù)據(jù)導(dǎo)出成文本文件

2.2.1 語(yǔ)法

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

scott>select * from emp;--執(zhí)行sql

scott>spool off--關(guān)閉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

--可以看到第一個(gè)spool之后，到關(guān)閉spool之間所有的查詢語(yǔ)句和查詢結(jié)果都會(huì)顯示到文本文件中。

2.2.2 設(shè)定sqlplus的變量

***有時(shí)候我們想去美化或者格式化文本文件的內(nèi)容，需要去設(shè)定sqlplus的變量

scott>show all--查詢當(dāng)前會(huì)話sqlplus的變量

可以直接輸入set去限定當(dāng)前會(huì)話的sqlplus變量

如：默認(rèn)的sqlplus登錄后提示符為sql>,

sql>set sqlprompt "_user>" 提示符就變?yōu)楫?dāng)前登錄用戶，如：scott>

常見和常用的有：

set echo off;--不顯示腳本中正在執(zhí)行的SQL語(yǔ)句

set term off;--不將結(jié)果顯示在屏幕上，直接導(dǎo)出到文件中

set heading off;--不輸出列名

set feedback off; --默認(rèn)行數(shù)大于6行會(huì)顯示 xx rows selected.

set linesize 100;--每行100個(gè)字符

set pagesize 800;--每頁(yè)800行，默認(rèn)24

set colsep |;--設(shè)定列之間的分隔符

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 導(dǎo)出數(shù)據(jù)模板

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--導(dǎo)出成txt文件

scott>@/tmp/spool.sql /tmp/emp.csv emp--導(dǎo)出成CSV文件

2.3 windows環(huán)境下ociuldr工具生成文本文件（ociuldr.exe需自己下載）

2.3.1 ociuldr導(dǎo)出例子

ociuldr 不是內(nèi)部工具，需要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文件，內(nèi)容為查詢的語(yǔ)句，不要有最后的分號(hào)

步4：雙擊out.bat

注：bat文件和ociuldr一定要在同一文件夾，sql文件不需要，只有保障路徑正確

2.3.2keyword詳解

User：指定目標(biāo)數(shù)據(jù)庫(kù)的用戶名、密碼和tnsname;

Sql : 設(shè)置所要執(zhí)行的sql文件；

Query：設(shè)置所要執(zhí)行的sql語(yǔ)句；

Field：設(shè)置每個(gè)field間的分隔字符串；

Record：設(shè)置每個(gè)record間的分隔字符串；

Rows：指定輸出多少行以后打印一條日志，可以更好的觀察進(jìn)度（默認(rèn)是1000000）

File：設(shè)置導(dǎo)出的數(shù)據(jù)的文件名（如：sqluldrData.txt）；

Log：設(shè)置生成的log文件；

Text：設(shè)置輸出的文件類型；

Charset：設(shè)置輸出的字符集；

Ncharset: 設(shè)置輸出的字符集；

支持按照不同的批量導(dǎo)出數(shù)據(jù)，這通過一個(gè)參數(shù)batch來(lái)實(shí)現(xiàn)

默認(rèn)一個(gè)batch是50萬(wàn)條記錄，如果不指定

batch為2就表示100萬(wàn)條記錄換一個(gè)文件

默認(rèn)這個(gè)選項(xiàng)值是0，就是指不生成多個(gè)文件。

field 分隔符

head 是否打印標(biāo)題，默認(rèn)為NO

可以嘗試使用ociuldr導(dǎo)出的控制文件將數(shù)據(jù)加載到數(shù)據(jù)庫(kù)中

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

3.sqlldr 將文本文件導(dǎo)入到數(shù)據(jù)庫(kù)中

3.1 概述

sqlldr為oracle自帶的工具，用于將文本文件導(dǎo)入到數(shù)據(jù)庫(kù)中。

[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命令很簡(jiǎn)單：sqlldr scott/tiger control=/tmp/emp2.ctl log=emp2.log

sqlldr相關(guān)的文件：（1）文本文件，需要導(dǎo)入的數(shù)據(jù)

（2）ctl控制文件,指明導(dǎo)入的文本，指明導(dǎo)入到的表信息，導(dǎo)入方式

（3）log日志文件，記錄導(dǎo)入過程

（4）錯(cuò)誤數(shù)據(jù)文件.bad及失敗文件.dsc(可以不指明)

sqlldr的控制文件

*.ctl格式為：

load

infile "d://test.txt" 外部數(shù)據(jù)文件

infile "d://test1.txt" 可指定多個(gè)數(shù)據(jù)文件 (通過多行 infile 語(yǔ)句實(shí)現(xiàn))

append into table test 向表中追加數(shù)據(jù)

fields terminated by "," 外部文件的數(shù)據(jù)以“，”分隔

trailing nullcols 表中的字段沒有對(duì)應(yīng)的值時(shí)填充空值

(

id integer external, integer external 表示插入的數(shù)據(jù)是string，如果只保留 integer，表示插入的數(shù)據(jù)是二進(jìn)制

name "upper(:name)", 將插入的值轉(zhuǎn)換為大寫

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

dt date"yyyy-mm-dd" 插入日期型數(shù)據(jù)

)

在append的位置還可以用以下列表中的一個(gè)值：

insert 向表中插入值，但要求表開始時(shí)為空

replace delete表中的數(shù)據(jù)，然后插入新值

append 向表中追加數(shù)據(jù)

truncate trunctate表，然后插入新值

也可以按照字符的位置來(lái)導(dǎo)入數(shù)據(jù)

test.txt的數(shù)據(jù)如下

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)--待驗(yàn)證

）

3.2 例1簡(jiǎn)單

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

dept.ctl的內(nèi)容如下：

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的數(shù)據(jù)如下

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需要轉(zhuǎn)換數(shù)據(jù)類型

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多個(gè)txt

--yugu_00000000000001.txt 結(jié)構(gòu)如下：

保單號(hào) |被保人×××|分公司|中支|險(xiǎn)種|責(zé)任|責(zé)任子碼|期次|頻率|金額|渠道|是否生存金|是否預(yù)派發(fā)|來(lái)源系統(tǒng)|給付日期|

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的結(jié)構(gòu)如下：

YEARMTH VARCHAR2(10) Y 應(yīng)付日期

ORGCODE1 VARCHAR2(24) Y 分公司

ORGCODE2 VARCHAR2(24) Y 中支

POLICYNO VARCHAR2(20) Y 保單號(hào)

CLASSCODE VARCHAR2(8) Y 險(xiǎn)種

SALEATTR VARCHAR2(10) Y 渠道

DELCODE VARCHAR2(3) Y 給付類型

DELNUM NUMBER(20) Y 期次

TYPENO VARCHAR2(2) Y 責(zé)任子碼

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 是否未來(lái)派發(fā)

--控制文件寫法： 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.表導(dǎo)出和導(dǎo)人實(shí)例

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)

)

--導(dǎo)成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內(nèi)容：

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

)

向AI問一下細(xì)節(jié)

oracle 備份與恢復(fù)1-數(shù)據(jù)庫(kù)數(shù)據(jù)與文本文件

猜你喜歡

最新資訊

相關(guān)推薦

相關(guān)標(biāo)簽