ORACLE 体系 - 14

【十四】数据移动

14.1 概念

1）数据移动源于数据仓库，它是逻辑对象层面的数据复制，数据移动有两种引擎：

①ORACLE_LOADER（Sqlload引擎）

②ORACLE_DATAPUMP（数据泵引擎）

两个引擎的区别是：ORACLE_DATAPUMP只能读取由它导出的文件，而ORACLE_LOADER可以读取任何它能解析的第三方文件格式。

2）数据移动主要包含两个方面内容

⑴创建外部表的方法，两种引擎都可以生成外部表数据，但用途和方法是不同的。

①Sqlload引擎生成的外部表是文本格式的，支持跨平台的不同数据库间的数据移动。

②Data pump引擎生成的外部表是二进制格式的，适用于Oracle 平台的数据库之间快速数据移动。

⑵数据泵技术(expdp/impdp)

作为替代传统逻辑备份的导入导出，实现数据在逻辑层面的快速复制与恢复。

14.2 Directory(目录)

①创建外部表必须使用Directory指定外部表的目的地，目录是数据库对象，相当于把物理目录映射成一个逻辑目录名；

②引入directory的好处是简化了在不同OS中对于物理目录路径的格式描述；

③通过Sqlload和Data pump两种方法创建外部表时都必须使用指定的directory。

14.3 sql*loader

14.3.1 sql*loader 原理：

1）将外部数据（比如文本型）导入oracle database。（可用于不同类型数据库数据迁移）

本质是在段（segment 表）重新insert 记录

①conventional：将记录插入到segment的HWM(高水位线）以下的块，要首先访问bitmap，来确定那些block 有free space；

②direct path：将记录插入到segment的HWM(高水位线）以上的从未使用过的块，绕过db_buffer，不检查约束，还可以关闭redo，也支持并行操作，加快插入速度。

传统插入数据和直接插入数据：

SQL> create table emp1 as select * from emp where 1=2;
SQL> insert into emp1 select * from emp; 传统方式数据
SQL> insert /*+ APPEND */ into emp1 select * from emp; 直接方式数据，必须commit后才能查看数据

14.3.3 sql*loader 用法：

SQLLDR keyword=value [,keyword=value,...]

可以看帮助信息$/u01/oracle/bin/sqlldr(回车）

14.4 外部表示例

14.4.1 使用ORACLE_LOADER引擎建立外部表

步骤1：模拟生成数据源

SQL>select empno||','||ename||','||sal||','||deptno from scott.emp;

步骤2：建立目录，生成平面表（数据源）

$mkdir -p /home/oracle/dir1
$vi /home/oracle/dir1/emp1.dat 粘贴步骤1的查询结果

步骤3：建立directory

名称为dir1指向物理目录/home/oracle/dir1：

SQL>create directory dir1 as '/home/oracle/dir1';

将dir1的对象权限授予scott和tim用户：

SQL>grant read,write on directory dir1 to scott,tim;

步骤4：使用ORACLE_LOADER引擎创建外部表emp1_ext

scott:
CREATE TABLE emp1_ext
(EMPNO NUMBER(4),
ENAME VARCHAR2(10),
SAL NUMBER(7,2),
DEPTNO NUMBER(2))
ORGANIZATION EXTERNAL
(TYPE ORACLE_LOADER
DEFAULT DIRECTORY dir1
ACCESS PARAMETERS (FIELDS TERMINATED BY ",")
LOCATION ('emp1.dat')
) REJECT LIMIT UNLIMITED;

步骤5：验证外部表

SQL> select * from emp1_ext;

REJECT LIMIT UNLIMITED 的意思是 select 时剔除不合格的行，而不会报错“ORA-30653: 已达到拒绝限制值”。

14.4.2 使用ORACLE_DATAPUMP引擎导出导入外部表

步骤1：为scott用户建立外部表emp2_ext

数据源是emp2.dmp文件，逻辑目录是dir1。

SQL> CREATE TABLE emp2_ext
ORGANIZATION EXTERNAL
( TYPE ORACLE_DATAPUMP
DEFAULT DIRECTORY dir1
LOCATION ('emp2.dmp'))
AS SELECT empno,ename,sal,deptno FROM scott.emp ;

步骤2：验证scott的外部表emp2_ext

SQL> select * from scott.emp2_ext

步骤3：为tim用户建立外部表emp3_ext, 同样读取数据源emp2.dmp

tim:
SQL> CREATE TABLE emp3_ext
(EMPNO NUMBER(4),
ENAME VARCHAR2(10),
SAL NUMBER(7,2),
DEPTNO NUMBER(2))
ORGANIZATION EXTERNAL
(
TYPE ORACLE_DATAPUMP
DEFAULT DIRECTORY dir1
LOCATION ('emp2.dmp')
) ;

步骤4：验证tim的外部表emp3_ext

SQL>select * from tim.emp3_ext

14.4.3 使用sqlldr将emp1.dat导入到scott下的emp1

步骤1：建立控制文件

$ vi /home/oracle/dir1/emp1.ctl
load data
infile '/home/oracle/dir1/emp1.dat'
insert --insert 插入表必须是空表，非空表用append
into table emp1
fields terminated by ','
optionally enclosed by '"'
(empno,ename,sal,deptno)

步骤2：在scott下建立emp1表（内部表结构），只要结构不要数据

SQL> create table scott.emp1 as select empno,ename,sal,deptno from scott.emp where 1=2;

步骤3：ORACLE_LOADER引擎导入（normal方式）

$ cd /home/oracle/dir1
$ sqlldr scott/scott control=emp1.ctl log=emp1.log

步骤4：验证结果

SQL> select * from scott.emp1;

上例的另一种形式是将数据源和控制文件合并在.ctl里描述

[oracle@work sqlldr]$ vi emp.ctl
load data
infile *
append
into table emp1
fields terminated by ','
optionally enclosed by '"'
(empno,ename,sal,deptno)
begindata
7369,SMITH,800,20
7499,ALLEN,1600,30
7521,WARD,1250,30
[oracle@prod sqlload]$ sqlldr scott/scott control=emp.ctl log=emp.log

the end ！！！

@jackman 共筑美好！