Oracle 10g数据迁移

在Oracle 10g中提供了多种数据迁移工具，除了仍然支持旧的工具exp/imp以及SQL*Loader之外，还提供了更高效的数据泵技术。本文简单介绍这几个工具的用法，并进行一定的对比。

Exp/imp

Exp/imp是Oracle中比较古老的工具了，早期甚至很多DBA使用exp/imp作为备份工具。严格来讲，exp/imp算不上是一个备份工具，因为它缺少对备份介质以及备份数据的管理，顶多可以作为小型数据库的一个备用的备份方式。即使是将exp/imp作为一个转储工具进行数据的导出和导入，现在也由基于数据泵技术的expdp/impdp取代了。本文讨论exp/imp只是出于比较的目的。

使用Exp导出数据

首先看一下exp命令的使用，使用命令exp help=y可以获得对应的帮助信息。

[oracle@localhost ~]$ exp help=y

USERID must be the first parameter on the command line.

Keyword Description (Default) Keyword Description (Default)

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

USERID username/password FULL export entire file (N)

BUFFER size of data buffer OWNER list of owner usernames

FILE output files (EXPDAT.DMP) TABLES list of table names

COMPRESS import into one extent (Y) RECORDLENGTH length of IO record

GRANTS export grants (Y) INCTYPE incremental export type

INDEXES export indexes (Y) RECORD track incr. export (Y)

DIRECT direct path (N) TRIGGERS export triggers (Y)

LOG log file of screen output STATISTICS analyze objects (ESTIMATE)

ROWS export data rows (Y) PARFILE parameter filename

CONSISTENT cross-table consistency(N) CONSTRAINTS export constraints (Y)

OBJECT_CONSISTENT transaction set to read only during object export (N)

FEEDBACK display progress every x rows (0)

FILESIZE maximum size of each dump file

FLASHBACK_SCN SCN used to set session snapshot back to

FLASHBACK_TIME time used to get the SCN closest to the specified time

QUERY select clause used to export a subset of a table

RESUMABLE suspend when a space related error is encountered(N)

RESUMABLE_NAME text string used to identify resumable statement

RESUMABLE_TIMEOUT wait time for RESUMABLE

TTS_FULL_CHECK perform full or partial dependency check for TTS

VOLSIZE number of bytes to write to each tape volume

TABLESPACES list of tablespaces to export

TRANSPORT_TABLESPACE export transportable tablespace metadata (N)

TEMPLATE template name which invokes iAS mode export

常用选项：

l USERID：用户身份标识，包括用户名和密码，出于安全考虑建议在命令行中只提供用户名部分，然后根据提示输入密码。

l FULL：设置为y的话，导出整个数据库。

l OWNER：schema的名字，用于导出指定schema下的所有对象。

l FILE：指定导出的转储文件。

l TABLES：指定要导出的表。

l TABLESPACES：指定要导出的表空间。

例如要导出soctt用户对应的整个schema，可以如下操作：

[oracle@localhost ~]$ exp scott file=scott.dmp owner=scott

Password:

……

. exporting pre-schema procedural objects and actions

. exporting foreign function library names for user SCOTT

. exporting PUBLIC type synonyms

. exporting private type synonyms

. exporting object type definitions for user SCOTT

About to export SCOTT's objects ...

. exporting database links

. exporting sequence numbers

. exporting cluster definitions

. about to export SCOTT's tables via Conventional Path ...

. . exporting table BONUS 0 rows exported

EXP-00091: Exporting questionable statistics.

. . exporting table DEPT 4 rows exported

EXP-00091: Exporting questionable statistics.

. . exporting table EMP 14 rows exported

EXP-00091: Exporting questionable statistics.

. . exporting table SALGRADE 5 rows exported

EXP-00091: Exporting questionable statistics.

. . exporting table SYS_TEMP_FBT

……

Export terminated successfully with warnings.

如果只是导出单个表，可以如下操作：

[oracle@localhost ~]$ exp scott file=scott.dmp tables=emp

导出表空间的操作如下：

[oracle@localhost ~]$ exp system file=users.dmp tablespaces=users

注意：导出表空间或者全库（full=y）时，用户必须有DBA权限。

使用Imp导入数据

Imp命令的帮助信息同样可以使用imp help=y获得，不过大部分选项与exp类似，这里不再一一列出。要导入刚才导出的schema或者表，可以分别运行命令如下：

[oracle@localhost ~]$ imp scott/tiger file=scott.dmp

[oracle@localhost ~]$ imp scott/tiger file=scott.dmp tables=emp

如果要导入的对象在数据库中已经存在，将会报错。如果一定要进行导入，可以使用ignore=y忽略错误强行导入。

要将原来属于某个schema的对象导入到其他的schema中，需要使用fromuser和touser选项，例如要讲scott.dmp文件中的emp表导入到hr这个schema中，命令如下：

[oracle@localhost ~]$ imp hr/hr file=scott.dmp tables=emp fromuser=scott touser=hr

SQL*Loader

SQL*Loader用于将外部数据加载到Oracle数据库，是一个强力的数据加载工具。可以通过sqlldr命令或者基于Web的企业管理器（EM）进行调用。在进行SQL*Loader数据载入时，需要用到几类文件：控制文件、输入文件、日志文件、坏文件和丢弃文件。其中控制文件是SQL*Loader进行数据载入时控制整个过程的关键文件，它指明要加载的数据文件，日志文件等的位置，以及如何分析和解释数据。输入文件是要加载的数据文件，它的内容需要组织成记录的形式。数据加载的过程以及错误消息会记录到日志文件中，如果数据记录无法识别，则会将该记录写入到坏文件中。

SQL*Loader基本用法

SQL*Loader的控制文件包含四个部分：

1. Load Data：一般只是标识控制文件的开始

2. Infile：指定输入文件名，也就是数据文件了。

3. Into Table：提示SQL*Loader将数据加载到Oracle的哪个表中，一般有四种加载方式：

l Insert 默认方式，但是加载时要求表为空，否则会报错。

l Append 在原有数据基础上追加数据。

l Replace 删除原有数据，替换成新装载的数据。

l Truncate 与Replace相同

4. Fields：字段说明，用来描述要加载到的表的结构。

下面通过一个简单的例子来看一下SQL*Loader的使用。首先在Oracle中建立一个表，准备用来接收要加载的数据。

SQL> create table student(

2 sid char(3),

3 sname varchar2(10)

);

然后在操作系统中建立一个控制文件student.ctl，内容如下:

Load data

infile student.dat

insert

into table hr.student

fields terminated by ',' optionally enclosed by '"'

(sid,sname)

Insert表示数据加载的方式为插入；into table hr.student表示要加载数据的表是hr这个schema下的student表；terminated by ','表示使用逗号作为字段的分隔符；optionally enclosed by '"'表示特殊数据需要使用双引号括起来。再准备一个数据文件，写入一些用于测试的数据，例如：

"001","Yangxin"

"002","Wangming"

"003","Liugang"

"004"

"005","Hezhi"

其中第四行数据故意少写了一个字段的值，这样将会导致这条记录加载失败，加载失败的记录会被写到badfile中。做好前面的准备工作，就可以进行数据加载了，执行命令：

[oracle@localhost ~]$ sqlldr hr/hr control=student.ctl

SQL*Loader: Release 10.2.0.1.0 - Production on Fri May 27 12:28:34 2011

Commit point reached - logical record count 5

加载完成后，在Oracle中进行查询如下，可以发现加载了四条记录。

SQL> select * from student;

SID SNAME

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

001 Yangxin

002 Wangming

003 Liugang

005 Hezhi

对于没有加载成功的记录，可以在student.bad文件中找到。修改上面的控制文件如下：

Load data

infile student.dat

discardfile student.dsc

truncate

into table hr.student

when (2-4) <> '001'

fields terminated by ',' optionally enclosed by '"'

(ssid,sname)

这里加入了加载数据的条件，要求第2-4位置的字符不能为'001'，不满足这个条件的数据将会被写到student.dsc文件中。执行sqlldr命令后，会发现"001","Yangxin"这行内容被写到了student.dsc文件中。

SQL*Loader的数据加载方法

SQL*Loader的数据加载由两种方法：Direct Path Load和Conventional Load，分别称为直接路径加载和常规加载。直接路径加载方式会将要加载的数据内容进行格式化，然后直接将数据块写入Oracle数据文件中，因此这种记载方式速度非常快。而常规加载方式则是通过执行SQL语句的方式进行数据加载，速度要慢很多。

二者的主要对比如下：

常规加载直接路径加载

使用commit进行数据提交使用data save保存数据，速度更快

生成redo条目只有特定条件下会生成redo

所有约束生效只有主键、Unique、Not null约束生效

会激活insert触发器不会激活insert触发器

可以加载数据到聚簇表不可以加载数据到聚簇表

加载期间允许其他用户修改表加载期间不允许其他用户修改表

要使用直接路径加载方式的话，只需要在sqlldr命令中加入选项direct=true，例如：

[oracle@localhost ~]$ sqlldr hr/hr control=student.ctl direct=true

Oracle 10g数据泵技术

数据泵技术是Oracle 10g提出的一种新的数据高速导出和导入的方法，可以自动管理多个并行的数据流。在Oracle 10g中，数据泵功能通过DBMS_DATAPUMP包提供，可以通过命令行接口和基于Web的企业管理器（EM）进行调用，完成数据的导出、导入和表空间的传输。相对于旧的exp/imp工具，数据泵技术不仅在性能方面有大幅的提高，同时也提供了平滑的对象选择方法，而且还支持断点重启功能。当数据泵的数据导出任务被人为或者意外中断时，可以从断点开始重启任务。

建立目录对象

要使用数据泵技术，需要先在Oracle数据库中建立目录对象。Oracle目录对象是在数据库中为操作系统的物理存储路径建立的一个逻辑结构。有了目录这个逻辑对象，用户就不必随时关心文件的实际存储位置，而且对文件的管理更加灵活。所有的目录对象的拥有者都是sys用户，因此用户要使用目录对象，需要提前进行授权。

建立目录对象可以使用EM或者SQL命令。以SQL命令为例：

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

Directory created.

之后我们将使用hr用户导出数据，把导出文件建立在这个目录上，为此需要授予hr用户对该目录的访问权限。

SQL> grant read,write on directory exp_dir to hr;

Grant succeeded.

使用expdp进行数据导出

使用Oracle 10g的数据泵技术进行数据的导出和导入，可以使用expdp/impdp命令工具。这两个命令都可以通过命令行参数执行，也可以交互式执行。

Expdp命令的常用选项如下：

ATTACH：该选项用于与已存在的导出作业建立关联。注意，如果使用ATTACH选项，在命令行除了连接字符串和ATTACH选项外，不能指定任何其他选项。可以通过查询DBA_DATAPUMP_JOBS获得系统中现有的作业信息。示例如下:

[oracle@localhost ~]$ expdp hr/hr ATTACH=ss.export_job

COMPRESSION：表示是否压缩数据库对象的元数据，这里只提供两个选项：METADATA_ONLY和NONE。在10g中这个选项的意义不大，因为元数据本身很小，压缩与否对最终导出的文件大小的影响甚微。11g中对这个选项进行了增强，真正的提供了数据压缩的功能。

CONTENT：该选项用于指定要导出的内容，默认值为ALL。CONTENT=DATA_ONLY，当设置CONTENT为ALL时，会导出对象元数据及对象数据；当设置为DATA_ONLY时，只导出对象数据；当设置为METADATA_ONLY时，只导出对象元数据。

示例如下:

expdp hr/hr DIRECTORY=exp_dir DUMPFILE=hr.dump CONTENT=METADATA_ONLY

DIRECTORY：指定转储文件和日志文件所在的目录，给定的参数是一个数据库中的目录对象，是通过CREATE DIRECTORY语句建立的。

DUMPFILE：用于指定转储文件的名称，默认名称为expdat.dmp。语法格式如下：

UMPFILE=[directory_object:]file_name [, ...]

directory_object用于指定目录对象名，file_name用于指定转储文件名。如果不给定目录对象，导出工具会自动使用DIRECTORY选项指定的目录对象。这个参数可以结合FILESIZE参数一起使用，达到生成多个转储文件的目的。注意，如果指定路径下已经存在待生成的导出文件，导出过程中将会报错退出。

expdp hr/hr DIRECTORY=exp_dir1 DUMPFILE=exp_dir2:test.dmp

EXCLUDE：用于控制在导出过程中哪些数据库对象不被导出。

EXCLUDE=object_type[:name_clause] [, ...]

object_type用于指定要排除的对象类型，name_clause用于指定要排除的具体对象名称。注意EXCLUDE选项和INCLUDE选项不能同时使用。该选项支持模糊匹配，非常好用的功能。另外，被指定不被导出的表上的约束、索引、触发器等均不会被导出。

示例如下:

expdp hr/hr DIRECTORY=exp_dir DUMPFILE=hr.dmp EXCLUDE=VIEW

FILESIZE：限定单个转储文件的最大容量，默认值是0，表示没有文件尺寸的限制。该选项与DUMPFILE选项一同使用。语法为：FILESIZE=integer[B | K | M | G]

FULL：是否以全库模式导出数据库。默认为N。FULL=y，为Y时,表示执行数据库的全库导出。

INCLUDE：指定导出哪些数据库对象类型或数据库对象，与EXCLUDE选项用法相同，功能相反。

注意INCLUDE选项和EXCLUDE选项不能同时使用。

PARALLEL：指定执行导出操作的并行度，默认值为1。语法为：PARALLEL=integer

注意，这个参数给出的并行度是一个真正能启用进程数的最大值。具体会启用多少个进程并行处理会受很多因素影响，例如生成转储文件的多少（不能多于文件数）、导出的数据量大小、CPU资源还有系统I/O资源等因素影响。另外，这个参数只有在Oracle 10g的企业版本中才可以使用。

SAMPLE：给出导出表数据的百分比，参数值可以取.000001~100（不包括100）。不过导出过程不会和这里给出的百分比一样精确，是一个近似值。语法如下：

SAMPLE=[[schema_name.]table_name:]sample_percent

示例如下:

SAMPLE="HR"."EMPLOYEES":50

SCHEMAS：按照SCHEMA模式导出，默认为当前用户。

TABLES：以表模式导出数据。可以同时导出多个表；支持通配符格式的导出；也支持只导出分区表中的某个分区。语法如下：

TABLES=[schema_name.]table_name[:partition_name] [, ...]

schema_name用于指定用户名，table_name用于指定导出的表名，partition_name用于指定要导出的分区名。

TABLESPACES：指定需要导出哪个表空间中的表数据。注意：Only the tables contained in a specified set of tablespaces are unloaded.也就是说，只有表空间里的表数据会被导出。

TABLESPACES=tablespace_name [, ...]

使用impdp进行数据导入

Impdp命令的大部分选项与expdp类似，只有少数不同，如下：

Remap_schema：将源schema的所有对象加载到目标schema中。

Remap_datafile：将源数据文件名转为目标数据文件名。

Remap_tablespace：将源表空间内的对象加载到目标表空间中。

简单举例，要从文件hr.dmp中导入表employees，命令如下：

[oracle@s1 ~]$ impdp hr/hr directory=exp_dir dumpfile=hr.dmp tables=employees

要导入整个schema，命令如下：

[oracle@s1 ~]$ impdp hr/hr directory=exp_dir dumpfile=hr.dmp schemas=hr

导入employees表，但是导入后表存放在test表空间中，命令如下：

[oracle@s1 ~]$ impdp hr/hr directory=exp_dir dumpfile=hr.dmp tables=employees remap_tablespace=users:test

使用外部表（External Table）

外部表是指数据不在数据库中的表，这个外部表对于Oracle数据库来说，就好像是一张视图，在数据库中可以像视图一样进行查询等操作。这个视图允许用户在外部数据上运行任何的查询语句，而不需要先将外部表中的数据装载进数据库中。不过需要注意是，外部数据表都是只读的，不能够更改。

建立外部表可以通过数据泵和SQL*Loader两种方式。先看第一种方式，使用数据泵将数据写到外部表中。

CREATE TABLE emp_ext

(first_name, last_name)

ORGANIZATION EXTERNAL

(TYPE ORACLE_DATAPUMP

DEFAULT DIRECTORY exp_dir

LOCATION ('emp1.exp') )

AS

SELECT first_name,last_name from hr.employees ;

建立完成后，就可以像查询普通表那样查询外部表了。

还有一种建立外部表的方式是通过SQL*Loader。如果你已经有了原本要加载到数据库中的数据文件，可以不再将数据加载到Oracle数据库中，而是使用SQL*Loader的语法建立一个外部表，外部表对应的数据内容就是原本要加载的数据文件的内容。

CREATE TABLE extab_employees

(employee_id NUMBER(4),

first_name VARCHAR2(20),

last_name VARCHAR2(25),

hire_date DATE)

ORGANIZATION EXTERNAL

( TYPE ORACLE_LOADER DEFAULT DIRECTORY extab_dat_dir

ACCESS PARAMETERS

( records delimited by newline

badfile extab_bad_dir:'empxt%a_%p.bad'

logfile extab_log_dir:'empxt%a_%p.log'

fields terminated by ','

missing field values are null

( employee_id, first_name, last_name,

hire_date char date_format date mask "dd-mon-yyyy“))

LOCATION ('empxt1.dat', 'empxt2.dat') )

PARALLEL REJECT LIMIT UNLIMITED;

我们可以看到上述语句的语法格式相对比较复杂，其实可以通过sqlldr命令来生成这段代码。假设还是前面我们执行sqlldr的环境，可以执行如下命令：

[oracle@localhost ~]$ sqlldr hr/hr control=student.ctl external_table=generate_only

命令执行后，会在student.log文件中找到生成外部表的SQL语句，复制到sqlplus界面中粘贴执行即可。

另外，关于数据泵技术的几个组件的关系如下图所示，可以作为一个参考。

ITPub博客

[转] Oracle数据迁移

Exp/imp

使用Exp导出数据

使用Imp导入数据

SQL*Loader

SQL*Loader基本用法

SQL*Loader的数据加载方法

Oracle 10g数据泵技术

建立目录对象

使用expdp进行数据导出

使用impdp进行数据导入

使用外部表（External Table）