说明:应业务需求需要将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
解决办法:使用数据泵将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/