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.表导出和导人实例
---------------------------------------------------------------------------------------
概述
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
)
