使用dblink和数据泵将数据由10g迁到11g

--源库：10.2.0.5 csdb
--目标库：11.2.0.4 orcl
源库和目标库tnsnames.ora文件中添加对方内容
ORCL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.101)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )
CSDB =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.102)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = csdb)
    )
  )
源端 tnsping orcl
目标端 tnsping csdb  
源库创建测试用户
create tablespace abctest
datafile '/oradata/CSDB/datafile/test_01.dbf' size 10M;

create user test identified by oracle_4U
default tablespace abctest
temporary tablespace temp; 

grant create session, resource to test;

CREATE TABLE test.worker
(Wno CHAR(9) PRIMARY KEY,
Wname CHAR(20) NOT NULL);

insert into test.worker values('003','sakiel');

commit;

select * from test.worker;

源端数据库相关参数
--parallel_max_servers设置为0，避免Bug 5453502
SQL> show parameter parallel_max  

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
parallel_max_servers                 integer     20
SQL> alter system set parallel_max_servers=0;

System altered.
目标端数据库参数
--确保cursor_sharing为EXACT
SQL> show parameter cursor_sha  

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
cursor_sharing                       string      EXACT
目标端导数据路径
SQL> select * from dba_directories;

OWNER                          DIRECTORY_NAME
------------------------------ ------------------------------
DIRECTORY_PATH
--------------------------------------------------------------------------------
SYS                            XMLDIR
/u01/app/oracle/product/11.2.0/dbhome_1/rdbms/xml

SYS                            ORACLE_OCM_CONFIG_DIR
/u01/app/oracle/product/11.2.0/dbhome_1/ccr/hosts/11g/state

SYS                            DATA_PUMP_DIR
/u01/app/oracle/admin/orcl/dpdump/


OWNER                          DIRECTORY_NAME
------------------------------ ------------------------------
DIRECTORY_PATH
--------------------------------------------------------------------------------
SYS                            ORACLE_OCM_CONFIG_DIR2
/u01/app/oracle/product/11.2.0/dbhome_1/ccr/state
目标端创建dblink
SQL> create database link dumplink connect to system identified by oracle_4U using 'CSDB';

Database link created.

SQL> select * from dual@dumplink;

D
-
X
由于源数据库的表空间不存在于目标数据库，导入时创建对象会报错，因此我们需要在目标数据库创建表空间
目标端运行下面语句获得创建表空间语句
SELECT 'create tablespace ' || tablespace_name || ' datafile ''' || REPLACE (file_name, 'CSDB', 'ORCL') || ''' size '  
    || TO_CHAR (CEIL (size_mb)) || ' M autoextend on;' as cmd  
   FROM (SELECT tablespace_name, file_name, (bytes / 1024 / 1024) size_mb  
           FROM dba_data_files@dumplink  
          WHERE tablespace_name IN (SELECT DISTINCT tablespace_name  
                                      FROM dba_segments@dumplink  
                                     WHERE owner IN ('TEST'))); 
目标端运行下面语句获得创建临时表空间语句，被导入的schema可能缺省的临时表空间并非temp
SELECT    'create temporary tablespace '  
      || tablespace_name  
      || ' tempfile '''  
      || REPLACE (file_name, 'CSDB', 'ORCL')  
      || ''' size '  
      || TO_CHAR (CEIL (bytes / 1024 / 1024))  
      || ' M autoextend on;' as cmd  
 FROM dba_temp_files@dumplink  
WHERE tablespace_name IN (SELECT temporary_tablespace  
                            FROM dba_users@dumplink  
                           WHERE username IN ('TEST') AND temporary_tablespace <> 'TEMP');  
目标端运行导入						   
impdp \'\/ as sysdba \' directory=DATA_PUMP_DIR network_link=dumplink logfile=imp_dump.log version=10.2.0.5 parallel=2 schemas='test'