Sql*load
1. sql*loader的文件有哪些？
日志文件：概述了作业的成功与失败以及所有相关错误的细节
错误文件（bad file）:从输入文件中抽取的行可能会被sqlldr丢弃（原因可能是这些行与控制文件所期望的格式不一致），也可能会被数据库丢失（例如，插入操作可能违反某个完整性约束），在上述两种情况下，这些记录都会被写入错误文件（bad file）
废弃文件（reject file）：如果成功的从输入文件中抽取记录，但是由于不匹配某些记录选择标准而被丢弃，那么这些记录会被写入废弃文件（reject file）
控制文件：一个文本文件，是sqlldr如何解释输入文件的内容以及如何利用从输入文件中抽取的记录
实验：将文件中的数据通过sql*loader加载到表中
create table t as select * from emp where 1=2;
将emp的数据输出到操作系统下的一个文件中：
SQL> set trims on
SQL> spool /u01/emp.txt
SQL> desc emp
SQL> select EMPNO||’,’||
2 ENAME||’,’||
3 JOB||’,’||
4 MGR||’,’||
5 HIREDATE||’,’||
6 SAL||’,’||
7 COMM||’,’||
8 DEPTNO
9 from emp;
EMPNO||’,’||ENAME||’,’||JOB||’,’||MGR||’,’||HIREDATE||’,’||SAL||’,’||COMM||’,’||
——————————————————————————–
7369,SMITH,CLERK,7902,17-DEC-80,800,,20
SQL> spool off
写控制文件：
vi /u01/load.ctl写入以下内容：
load data
infile ‘/u01/emp.txt’
badfile ‘/u01/bad.emp’
discardfile ‘/u01/discadr.emp’
append
into table t
fields terminated by ‘,’
(EMPNO,
ENAME,
JOB,
MGR,
HIREDATE,
SAL,
COMM,
DEPTNO)
保存退出
开始加载数据：
sqlldr scott/tiger control=/u01/load.ctl log=/u01/emp.log
选项介绍：
1. trailing nullcols
如果一条记录中的结尾字段不存在任何相关占位列插入null值
控制文件内容如下：
vi /u01/load.ctl
load data
infile ‘/u01/emp.txt’
badfile ‘/u01/bad.emp’
discardfile ‘/u01/discadr.emp’
replace
into table t
fields terminated by ‘,’
(EMPNO,
ENAME,
JOB,
MGR,
HIREDATE,
SAL,
COMM,
DEPTNO)
/u01/emp.txt内容如下：
7521,WARD,SALESMAN,7698,22-FEB-81,1250,500,
加载数据：
sqlldr scott/tiger control=load.ctl
SQL> select * from t;
no rows selected
发现没有数据
在控制文件中加入trailing nullcols如：
load data
infile ‘/u01/emp.txt’
badfile ‘/u01/bad.emp’
discardfile ‘/u01/discadr.emp’
replace
into table t
fields terminated by ‘,’
trailing nullcols
(EMPNO,
ENAME,
JOB,
MGR,
HIREDATE,
SAL,
COMM,
DEPTNO)
sqlldr scott/tiger control=load.ctl
SQL> select * from t;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
—– ———- ——— ———- ——— ———- ———- ———-
7521 WARD SALESMAN 7698 22-FEB-81 1250 500
发现数据已经加载到表中，deptno为null
2. 按照数据文件中的日期格式将数据加载到表中：
vi /u01/load.ctl
load data
infile ‘/u01/emp.txt’
badfile ‘/u01/bad.emp’
discardfile ‘/u01/discadr.emp’
replace
into table t
fields terminated by ‘,’
trailing nullcols
(EMPNO,
ENAME,
JOB,
MGR,
HIREDATE,
SAL,
COMM,
DEPTNO)
/u01/emp.txt数据如下：
7521,WARD,SALESMAN,7698,2001-01-01 19:14:13,1250,500,30
sqlldr scott/tiger control=/u01/load.ctl
SQL> select * from t;
no rows selected
发现日期的格式为yyyy-mm-dd hh24:mi:ss,数据库默认的日期格式为dd-mm-yy,可以将控制文件更改如下：
vi /u01/load.ctl
load data
infile ‘/u01/emp.txt’
badfile ‘/u01/bad.emp’
discardfile ‘/u01/discadr.emp’
replace
into table t
fields terminated by ‘,’
trailing nullcols
(EMPNO,
ENAME,
JOB,
MGR,
HIREDATE date “yyyy-mm-dd hh24:mi:ss”,
SAL,
COMM,
DEPTNO)
export NLS_LANG=american
export NLS_DATE_FORMAT=’yyyy-mm-dd hh24:mi:ss’
sqlldr scott/tiger control=/u01/load.ctl
SQL> col empno for 9999
SQL> col deptno for 99
SQL> col mgr for 9999
SQL> col ENAME for a5
SQL> select * from t;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
—– —– ——— —– ——————- ———- ———- ——
7521 WARD SALESMAN 7698 2001-01-01:19:14:13 1250 500 30
3. 使用函数，对数据文件中的数据进行加工
vi /u01/load.ctl
load data
infile ‘/u01/emp.txt’
badfile ‘/u01/bad.emp’
discardfile ‘/u01/discadr.emp’
replace
into table t
fields terminated by ‘,’
trailing nullcols
(EMPNO,
ENAME,
JOB “substr(:job,1,3)”,
MGR,
HIREDATE,
SAL,
COMM,
DEPTNO)
/u01/emp.txt的内容如下：
7521,WARD,SALESMAN,7698,22-FEB-81,1250,500,30
sqlldr scott/tiger control=/u01/load.ctl
SQL> select * from t;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
—– ———- ——— ———- ——— ———- ———- ———-
7521 WARD SAL 7698 22-FEB-81 1250 500 30
4. 跳过数据列
vi /u01/load.ctl
load data
infile ‘/u01/emp.txt’
badfile ‘/u01/bad.emp’
discardfile ‘/u01/discadr.emp’
replace
into table t
fields terminated by ‘,’
trailing nullcols
(EMPNO,
ENAME filler,
JOB,
MGR,
HIREDATE,
SAL,
COMM,
DEPTNO)
/u01/emp.txt的内容如下：
7521,WARD,SALESMAN,7698,22-FEB-81,1250,500,30
sqlldr scott/tiger control=/u01/load.ctl
SQL> select * from t;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
—– ———- ——— ———- ——— ———- ———- ———-
7521 SALESMAN 7698 22-FEB-81 1250 500 30
5. 选择数据
vi /u01/load.ctl
load data
infile ‘/u01/emp.txt’
badfile ‘/u01/bad.emp’
discardfile ‘/u01/discadr.emp’
replace
into table t
when deptno=’30’
fields terminated by ‘,’
trailing nullcols
(EMPNO,
ENAME filler,
JOB,
MGR,
HIREDATE,
SAL,
COMM,
DEPTNO)
/u01/emp.txt内容如下：
7369,SMITH,CLERK,7902,17-DEC-80,800,,20
7499,ALLEN,SALESMAN,7698,20-FEB-81,1600,300,30
7521,WARD,SALESMAN,7698,22-FEB-81,1250,500,30
sqlldr scott/tiger control=/u01/load.ctl
select * from t;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
—– ———- ——— ———- ——— ———- ———- ———-
7499 SALESMAN 7698 20-FEB-81 1600 300 30
7521 SALESMAN 7698 22-FEB-81 1250 500 30
6. 输入空值
vi /u01/load.ctl
load data
infile ‘/u01/emp.txt’
badfile ‘/u01/bad.emp’
discardfile ‘/u01/discadr.emp’
replace
into table t
fields terminated by ‘,’
trailing nullcols
(EMPNO,
ENAME,
JOB,
MGR,
HIREDATE,
SAL,
COMM nullif deptno=’30’,
DEPTNO)
/u01/emp.txt内容如下：
7369,SMITH,CLERK,7902,17-DEC-80,800,,20
7499,ALLEN,SALESMAN,7698,20-FEB-81,1600,300,30
7521,WARD,SALESMAN,7698,22-FEB-81,1250,500,30
sqlldr scott/tiger control=/u01/load.ctl
SQL> select * from t;
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 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 30
7. 直接加载数据
从表的高水位线后面直接加载数据
常规加载方法：
SQL> create table t as select * from emp where 1=2;
Table created.
控制文件如下：
load data
infile ‘/u01/emp.txt’
badfile ‘/u01/bad.emp’
discardfile ‘/u01/discadr.emp’
insert
into table t
fields terminated by ‘,’
trailing nullcols
(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
7521,WARD,SALESMAN,7698,22-FEB-81,1250,500,30
开始常规加载：
sqlldr scott/tiger control=/u01/load.ctl log=/u01/emp.log
1* select * from t
SQL> /
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
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
SQL> select EXTENT_ID,BLOCK_ID,BLOCKS from dba_extents where SEGMENT_NAME=’T’;
EXTENT_ID BLOCK_ID BLOCKS
———- ———- ———-
0 385 8
analyze table scott.t compute statistics;
SQL> select NUM_ROWS,BLOCKS,EMPTY_BLOCKS from dba_tables where TABLE_NAME=’T’;
NUM_ROWS BLOCKS EMPTY_BLOCKS
———- ———- ————
14 5 3
SQL> delete scott.t;
14 rows deleted.
SQL> commit;
Commit complete.
SQL> analyze table scott.t compute statistics;
Table analyzed.
SQL> select NUM_ROWS,BLOCKS,EMPTY_BLOCKS from dba_tables where TABLE_NAME=’T’;
NUM_ROWS BLOCKS EMPTY_BLOCKS
———- ———- ————
0 5 3
使用直接路径加载：
SQL> delete t;
14 rows deleted.
SQL> commit;
Commit complete.
SQL> analyze table t compute statistics;
Table analyzed.
SQL> select NUM_ROWS,BLOCKS,EMPTY_BLOCKS from user_tables where TABLE_NAME=’T’;
NUM_ROWS BLOCKS EMPTY_BLOCKS
———- ———- ————
0 5 3
sqlldr scott/tiger control=/u01/load.ctl direct=y log=/u01/emp.log
SQL> analyze table scott.t compute statistics;
Table analyzed.
SQL> select NUM_ROWS,BLOCKS,EMPTY_BLOCKS from dba_tables where TABLE_NAME=’T’;
NUM_ROWS BLOCKS EMPTY_BLOCKS
———- ———- ————
14 9 7
SQL> select EXTENT_ID,BLOCK_ID,BLOCKS from dba_extents where SEGMENT_NAME=’T’;
EXTENT_ID BLOCK_ID BLOCKS
———- ———- ———-
0 385 8
1 393 8
数据库目录
创建目录对象：
conn scott/tiger
create directory dump_dir as ‘/u01/dir’;
SQL> conn /as sysdba
SQL> create directory dump_dir as ‘/u01/dir’;
SQL> grant read,write on directory dump_dir to scott;
SQL> grant all on directory dump_dir to scott;
select * from all_directories;———owner仍然是sys
数据泵
1. 导出导入表
创建数据泵目录：
SQL> conn /as sysdba
SQL> create directory dump_dir as ‘/u01/dir’;
SQL> grant read,write on directory dump_dir to scott;
SQL> grant all on directory dump_dir to scott;
导出emp，dept表：
[oracle@nylg ~]$ expdp scott/tiger directory=dump_dir dumpfile=exp%U.dmp tables=emp,dept job_name=exp
[oracle@nylg ~]$ cd /u01/dir
[oracle@nylg dir]$ ls
exp01.dmp export.log
[oracle@nylg dir]$ vi exp01.dmp
[oracle@nylg dir]$ vi export.log
[oracle@nylg dir]$ sqlplus scott/tiger
SQL> drop table emp purge;
SQL> drop table dept purge;
SQL> exit
[oracle@nylg dir]$ impdp scott/tiger directory=dump_dir dumpfile=exp01.dmp tables=emp,dept job_name=imp
[oracle@nylg dir]$ sqlplus scott/tiger
SQL> select * from tab;
SQL> select count(*) from dept;
SQL> select count(*) from emp;
查看约束是否存在：
SQL> select CONSTRAINT_NAME from user_constraints where TABLE_NAME=’EMP’;
select CONSTRAINT_NAME from user_constraints where TABLE_NAME=’DEPT’
2. 导出导入用户
导出scott和hr：
[oracle@nylg ~]$ expdp system/a directory=dump_dir dumpfile=schema%U.dmp schemas=scott,hr job_name=schema
删除scott：
SQL> drop user scott cascade;
再将scott导入：
[oracle@nylg dir]$ impdp system/a directory=dump_dir dumpfile=schema01.dmp schemas=scott job_name=schema
[oracle@nylg dir]$ sqlplus scott/tiger
SQL> select * from tab;
将scott改名为s导入到数据库：
[oracle@nylg dir]$ impdp system/a directory=dump_dir dumpfile=schema01.dmp remap_schema=scott:s schemas=scott job_name=schema
[oracle@nylg dir]$ sqlplus / as sysdba
SQL> alter user s identified by s;
SQL> conn s/s
SQL> select * from tab;
3. 导出全库：
导出全库：
expdp system/a directory=dump_dir dumpfile=full%U.dmp full=y job_name=full parallel=4
4. 数据泵的高级用法
(1)通过指定filesize参数，从而控制导出文件的大小
expdp system/a full=y job_name=full parallel=4 dumpfile=dump_dir:full1%U.dmp,test_dir:full2%U.dmp filesize=20M
表示在两个目录下循环生成多个20M的文件
(2) 过滤某些表
vi /u01/exp.par
exclude=table:”like ‘EMPLOYEES%'”
schemas=hr
保存退出
先看一下hr中的表：
[oracle@nylg dir]$ sqlplus hr/hr
SQL> select * from tab;
使用参数文件/u01/exp.par导出hr用户，排除EMPLOYEES表：
expdp system/a directory=dump_dir dumpfile=tab%U.dmp job_name=exp parfile=/u01/exp.par
从以上的日志中可以看出EMPLOYEES没有导出
[oracle@nylg dir]$ impdp system/a directory=dump_dir dumpfile=tab01.dmp remap_schema=hr:test job_name=imp
[oracle@nylg dir]$ sqlplus / as sysdba
SQL> alter user test identified by oracle;
SQL> conn test/oracle
SQL> select * from tab;
通过将导出hr用户数据导入到数据库用户test中可以看出没有EMPLOYEES表
可以使用in进行排除表：
vi /u01/exp.par
exclude=table:”in(‘EMPLOYEES’,’DEPARTMENTS’)”
schemas=hr
[oracle@nylg dir]$ expdp system/a directory=dump_dir dumpfile=tab%U.dmp job_name=exp parfile=/u01/exp.par
从以上的日志中可以看出EMPLOYEES和DEPARTMENTS都没有导出
（3）只导出指定类型的对象（比如只导出存储过程等）
使用此选项必须使用参数文件：
vi /u01/exp.par
include=function
include=procedure
include=package
include=view:”like ‘PRODUCT%'”
schemas=hr,oe
expdp system/a directory=dump_dir dumpfile=tab%U.dmp job_name=exp parfile=/u01/exp.par
从以上日志中可以看出没有表
（4）只导出数据不导表定义：
通过content控制导出那些数据
metadata_only:只导出对象的定义信息
data_only:只导出表里实际的数据
all：导出对象的定义信息以及实际的数据，这是默认的导出方式。
例如：只导出hr下的对象定义信息：
expdp system/a directory=dump_dir dumpfile=schema%U.dmp schemas=hr content=metadata_only
[oracle@nylg dir]$ impdp system/a directory=dump_dir dumpfile=schema01.dmp remap_schema=hr:test
[oracle@nylg dir]$ sqlplus / as sysdba
SQL> alter user test identified by a;
SQL> conn test/a
SQL> select * from tab;
SQL> select count(*) from EMPLOYEES;
发现没有数据
SQL> conn hr/hr
SQL> select count(*) from EMPLOYEES
（5）只导出符合条件的数据行
必须用参数文件来完成
vi /u01/exp.par
tables=employees
query=employees:”where department_id=40 order by employee_id”
执行导出：
expdp hr/hr directory=dump_dir dumpfile=emp%U.dmp parfile=/u01/exp.par
将employees表导入到scott中进行验证：
impdp system/a directory=dump_dir dumpfile=emp01.dmp remap_schema=hr:scott
sqlplus scott/tiger
select * from employees;
只有40号部门的数据
conn hr/hr
select * from employees;
原表中有107行
（6）对数据库中的数据采样后，导出采样后的数据，可以用作测试数据：
expdp hr/hr directory=dump_dir dumpfile=schema%U.dmp schemas=hr sample=30
[oracle@nylg dir]$ impdp system/a directory=dump_dir dumpfile=schema01.dmp remap_schema=hr:scott
[oracle@nylg dir]$ sqlplus scott/tiger
SQL> select count(*) from employees;
SQL> conn hr/hr
SQL> select count(*) from employees
SQL> select 33/107 from dual;
(7)中断导出任务后，从中断处继续执行导出任务
在使用数据泵导出数据的时候，可以主动的或由于异常而中断任务
实验：
expdp system/a directory=dump_dir dumpfile=full%U.dmp full=y job_name=full
Export> stop_job
Are you sure you wish to stop this job ([yes]/no): yes
主动中断：ctrl+c
重启导出任务：
expdp system/a attach=full
Export> parallel=2
设置完成后，重新启动导出任务
Export> start_job
start_job开始后，在后台运行，可以调到前台来运行：
Export> continue_client
（8）分布式网络导出导入：
实验：将linux下scott用户导入到windows下
把linux下的scott用户导到window下的u1
u1可以不存在
步骤:
1.在window端：
conn system/a
create database link hndx connect to scott identified by tiger using ‘orcl’;
2.在window端写参数文件：
network_link=hndx
remap_schema = scott:u1
remap_tablespace = users:example
schemas=scott
job_name = cross_network
3.在linux下给scott受权限
grant exp_full_database to scott;
grant imp_full_database to scott;
4.在window端执行导入：
impdp system/a parfile=c:\imp.txt
外部表
1. 外部表的定义：
表在数据库中是以段存在的，而外部表不是以段存在的，外部表作为数据字典中定义的对象存在
数据存储在操作系统文件里，可以像查询其他表一样查询外部表不能对外部表执行DML操作
2. 外部表的类型：
有两种：一种是sql_load，一种是datapump
3. 创建一个sql_loader的外部表：
生成外部表的数据文件：
[oracle@nylg ~]$ sqlplus scott/tiger
SQL> set trims on
SQL> spool /u01/dir/dept.load
SQL> desc dept
SQL> select DEPTNO||’,’||
DNAME||’,’||
LOC from dept;
SQL> spool off
SQL> exit
创建外部表：
create table test_ext
(
deptno number(2),
dname varchar2(14),
loc varchar2(13)
)
organization external
(
type oracle_loader
default directory dump_dir
access parameters
(
records delimited by newline
badfile ‘test.bad’
discardfile ‘test.dis’
logfile ‘test.log’
fields terminated by ‘,’
missing field values are null
)
location (‘dept.load’)
);
访问外部表：
select * from test_ext;
sql_loader外部表可以通过sql*load来生成创建语法：
控制文件的内容如下：
load data
infile ‘/u01/emp.txt’
badfile ‘/u01/bad.emp’
discardfile ‘/u01/discadr.emp’
insert
into table emp
fields terminated by ‘,’
trailing nullcols
(EMPNO,
ENAME,
JOB,
MGR,
HIREDATE,
SAL,
COMM,
DEPTNO)
生成外部表的创建语句：
sqlldr userid=scott/tiger control=/u01/load.ctl external_table=generate_only
vi load.log内容如下：
将以上语句的表名，目录名还有文件名改为我们想要的，然后执行此语句，就可以得到我们想要的外部表
4. 创建一个datapump类型的外部表：
生成文件：
SQL> create table test_ext
(
deptno,
dname
)
organization external(
type oracle_datapump
default directory dump_dir
location(‘load.test’)
)
as select deptno,dname from dept;
以上语句执行完后，就生成了一个文件：load.test
进入hr用户创建一个外部表，数据文件使用以上生成的load.test
SQL> conn hr/hr
Connected.
SQL> create table test_ext
(
deptno NUMBER(2),
dname VARCHAR2(14)
)
organization external(
type oracle_datapump
default directory dump_dir
location(‘load.test’)
)
SQL> select * from test_ext;
