使用数据泵迁移用户数据

说明：应业务需求需要将Windows下oracle（11.2.0.1.0）用户userbank下所有数据迁移到Linux下oracle（11.2.0.4.0）数据库中

解决办法：使用数据泵将Windows下oracle数据库userbank下数据导出，然后将导出的数据导入到Linux下oracle数据库中

一、Windows下

创建目录E:\expbk，用于存放数据

SQL> create directory my_dir as 'E:\expbk';
Directory created

SQL> grant read,write on directory my_dir to userbank;

创建E:\expbk\expdp.txt文件，内容如下：
userid=userbank/userbank
directory=my_dir
parallel=2
job_name=pump_userbank
content=all
dumpfile=userbank.dmp
schemas=userbank
logfile=userbank.log

导出数据
C:\Users\Administrator>expdp parfile=E:\expbk\expdp.txt

将导出的数据文件USERBANK.DMP上传到Linux数据库服务器下

二、Linux下操作

1、创建和Windows下同名的表空间
SQL> create tablespace TABLESPACE_BANK datafile '/u01/app/oracle/oradata/nba/tablespace_bank.dbf' size 30M;

2、创建相应目录用于存放数据文件
mkdir /home/oracle/expbk
SQL> create directory my_dir as '/home/oracle/expbk';

3、创建参数文件
vim /home/oracle/expbk/impdp.txt
userid=userbank/userbank
directory=my_dir
job_name=pump_schema_userbank
schemas=userbank
content=all
parallel=4
dumpfile=USERBANK.DMP
logfile=imp_userbank.log

注：如果要将导出的数据导入到其他库中时对应不同的用户和表空间，需要下面两个参数：
remap_schema=userbank:china
remap_tablespace=tablespace_bank:china_bank

4、创建和Windows同名的用户
SQL> create user userbank default tablespace tablespace_bank identified by userbank;

User created.

SQL> grant connect,resource to userbank;

Grant succeeded.

SQL> grant read,write on directory my_dir to userbank;

Grant succeeded.

SQL> grant create view to userbank;

Grant succeeded.

注：由于导出的数据中包含视图，所以用于接受数据的用户必须要有创建视图的权限

5、导入数据
[oracle@ora11gR2 expbk]$ impdp parfile=/home/oracle/expbk/impdp.txt

Import: Release 11.2.0.4.0 - Production on Mon Apr 25 20:01:02 2016

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "USERBANK"."PUMP_SCHEMA_USERBANK" successfully loaded/unloaded
Starting "USERBANK"."PUMP_SCHEMA_USERBANK":  userbank/******** parfile=/home/oracle/expbk/impdp.txt
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "USERBANK"."CARDINFO"                       8.640 KB       7 rows
. . imported "USERBANK"."DEPOSIT"                        6.078 KB      10 rows
. . imported "USERBANK"."TRADEINFO"                      7.648 KB      28 rows
. . imported "USERBANK"."USERINFO"                       6.890 KB       6 rows
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_SPEC
Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE
Processing object type SCHEMA_EXPORT/PACKAGE/COMPILE_PACKAGE/PACKAGE_SPEC/ALTER_PACKAGE_SPEC
Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
ORA-39082: Object type ALTER_PROCEDURE:"USERBANK"."USP_PAGINGDISPLAY" created with compilation warnings
Processing object type SCHEMA_EXPORT/VIEW/VIEW
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "USERBANK"."PUMP_SCHEMA_USERBANK" completed with 1 error(s) at Mon Apr 25 20:01:13 2016 elapsed 0 00:00:09

6、验证结果
SQL> conn userbank/userbank
Connected.
SQL> select * from tab;

TNAME                                               TABTYPE          CLUSTERID
------------------------------------------------------------------------------------------ --------------------- ----------
CARDINFO                                           TABLE
DEPOSIT                                            TABLE
TRADEINFO                                           TABLE
USERINFO                                           TABLE
VW_CARDINFO                                           VIEW
VW_ONEUSERINFO                                           VIEW
VW_TRADEINFO                                           VIEW
VW_USERINFO                                           VIEW

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/30373263/viewspace-2094450/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/30373263/viewspace-2094450/