Oracle数据迁移

本文主要提及使用exp,imp以及Oracle数据泵等工具如何进行数据迁移：

一、数据迁移工具

1、数据迁移工具

（1）数据泵

（2）外部表：external table

（3）数据加载工具：sqlloader可以将非ORACLE数据库的数据加载到ORACLE数据库中

（4）数据库备份工具：rman,recover manager恢复管理器

2、逻辑导入导出工具

（1）exp:逻辑导出工具

（2）imp:逻辑导入工具

3、数据泵

（1）expdp:数据泵逻辑导出工具export data pump

（2）impdp:数据泵逻辑导入工具import data pump

4、数据库导出模式

（1）全库导出

（2）导出用户

（3）导出表空间

（4）导出表（数据泵）

二、数据迁移实例

使用exp,imp导入导出数据

1、全库导出

mkdir -p /home/oracle/backup/exp创建导出目录

exp system/oracle file=all.dmp log=all_exp.log full=y

2、导出用户

select username from dba_users;先查看当前数据库拥有哪些用户

（1）单用户hr导出

单用户hr导入，在目标库中进行，导入库中需要提前创建用户hr,并赋予相应权限

（2）多用户导出

多用户导入

3、导出表

（1）单表导出

导入

（2）多表导出，导入

使用数据泵导入导出数据

通过调用dbms_datapump系统包来完成导入导出操作

1、需要数据库创建目录对象，要把目录对象的读写权限授予相应的用户。

（1）在操作系统中创建目录

mkdir -p /home/oracle/backup/dir

（2）在数据库中创建目录对象，指向上一步创建的目录

create directory dbpump_dir as ‘/home/oracle/backup/dir’;

（3）给相应用户读写权限，用到什么用户就赋予它读写权限

grant read,write on directory dbpump_dir to system;

（4）开始导入导出

2、普通导入导出

3、通过参数文件导入导出数据

创建pfile.txt

PARALLEL=1

CLUSTER=NO

COMPRESSION=ALL

DUMPFILE=EXPORT_exp.DMP  

DIRECTORY=DBPUMP_DIR   

LOGFILE=EXPORT.LOG

TABLES=

(

 HR.EMPLOYEES,HR.DEPARTMENTS

 )

QUERY=

(

 "EMPLOYEES:WHERE DEPARTMENT_ID > 10",

 "DEPARTMENTS:WHERE DEPARTMENT_NAME LIKE '%IT%'"

)

导入同样需要在目标库建立数据库目录对象

create directory dbpump_dir as ‘/home/oracle/backup/dir1’;

4、通过参数文件导入导出用户

pfile_user.txt

PARALLEL=1

CLUSTER=NO

COMPRESSION=ALL

DUMPFILE=EXPORT_USER.DMP  

DIRECTORY=DBPUMP_DIR   

LOGFILE=EXPORT_USER.LOG

SCHEMAS=U2,U1

导入用户

5、通过参数文件导入导出表空间

pfile_tbs.txt

PARALLEL=1

CLUSTER=NO

COMPRESSION=ALL

DUMPFILE=EXPORT_TBS.DMP  

DIRECTORY=DBPUMP_DIR   

LOGFILE=EXPORT_TBS.LOG

TABLESPACES=TBS1,TBS2,TBS3

（1）要把目标库的表空间创建好

（2）把表空间所属对象创建好

（3）赋予相应对象权限

导入

三、外部表

ORACLE外部表用来存取数据库以外的文本文件（test file）或ORACLE专属格式文件。因此，建立外部表时不会产生段，区，块等数据结构，只有与表相关的定义放在数据字典中，外部表，就是存储在数据库外的表，当存取时才能从ORACLE专属格式文件中取得数据，外部表仅供查询，不能对外部表的内容修改。

1、创建目录对象并授权，从9i开始，ORACLE数据库若要存取文件系统，就必须使用目录对象，以相对路径方式存取路径，强化数据库的安全性。

2、从数据库导出数据到外部表

create table ext_emp_query

(first_name,last_name,department_name)

organization external

(type oracle_datapump

default directory tab_dir

location('emp1.exp','emp2.exp','emp3.exp')

)

parallel

as

select e.first_name,e.last_name,d.department_name

from employees e,departments d

where e.department_id=d.department_id and

d.department_name in ('Marketing','Purchaing')

3、外部插入数据

create table hr.ext_tab2

(id number,name varchar2(20))

organization external

(type oracle_loader

default directory tab_dir

location

(tab_dir:'ext_tab1',

tab_dir:'ext_tab2')

);

创建完成后需要自己到指定目录创建文件，如果文件提前存在，那么将无法查询到文件内容

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

1