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

采用dblink的impdp方式进行数据迁移。

源端： 172.16.84.71/orcl  11.2.0.4
目标端： 172.16.73.45/orcl 12c

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

1.目标端创建dblink

sqlplus / as sysdba

create database link DBLINK_FOR_IMPDP
  connect to system identified by pasddddd
  using '(DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 172.16.84.71)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = orcl)
    )
  )';

----测试dblink

select 1 from dual@dblink_for_impdp;

         1
----------
         1

2.目标端创建对应的表空间

Prompt Tablespace REPORT_BI_CFG;
--
-- REPORT_BI_CFG  (Tablespace) 
--
CREATE TABLESPACE REPORT_BI_CFG DATAFILE 
  '/data01/oradata01/orcl/REPORT_BI_CFG01.dbf' SIZE 1G AUTOEXTEND ON NEXT 100M MAXSIZE 30G
LOGGING
ONLINE
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT AUTO
FLASHBACK ON;


Prompt Tablespace orcl_ADMIN;
--
-- orcl_ADMIN  (Tablespace) 
--
CREATE TABLESPACE orcl_ADMIN DATAFILE 
  '/data01/oradata01/orcl/orcl_admin_data01.dbf' SIZE 1G AUTOEXTEND ON NEXT 100M MAXSIZE 30G,
  '/data01/oradata01/orcl/orcl_admin_data02.dbf' SIZE 1G AUTOEXTEND ON NEXT 100M MAXSIZE 30G,
  '/data01/oradata01/orcl/orcl_admin_data03.dbf' SIZE 1G AUTOEXTEND ON NEXT 100M MAXSIZE 30G,
  '/data01/oradata01/orcl/orcl_admin_data05.dbf' SIZE 1G AUTOEXTEND ON NEXT 100M MAXSIZE 30G,
  '/data01/oradata01/orcl/orcl_admin_data04.dbf' SIZE 1G AUTOEXTEND ON NEXT 100M MAXSIZE 30G,
  '/data01/oradata01/orcl/orcl_admin_data08.dbf' SIZE 1G AUTOEXTEND ON NEXT 100M MAXSIZE 30G,
  '/data01/oradata01/orcl/orcl_admin_data07.dbf' SIZE 1G AUTOEXTEND ON NEXT 100M MAXSIZE 30G,
  '/data01/oradata01/orcl/orcl_admin_data06.dbf' SIZE 1G AUTOEXTEND ON NEXT 100M MAXSIZE 30G,
  '/data01/oradata01/orcl/orcl_admin_data10.dbf' SIZE 1G AUTOEXTEND ON NEXT 100M MAXSIZE 30G,
  '/data01/oradata01/orcl/orcl_admin_data09.dbf' SIZE 1G AUTOEXTEND ON NEXT 100M MAXSIZE 30G
LOGGING
ONLINE
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT AUTO
FLASHBACK ON;

3.检查源端和目标端的字符集

select userenv('language') from dual;

USERENV('LANGUAGE')
----------------------------------------------------
AMERICAN_AMERICA.AL32UTF8

4. 目标端通过dblink方式impdp导入数据

impdp \"/ as sysdba \" directory=DATA_PUMP_DIR logfile=nss_20220726_impdp.log network_link=DBLINK_FOR_IMPDP schemas=orcl_ADMIN ,orcl_COMMON ,orcl_DL ,orcl_GANS ,orcl_GD ,orcl_GX ,orcl_RO ,orcl_RW ,orcl_SW ,orcl_XIAN ,orcl_ZJ  CONTENT=all exclude=statistics PARALLEL=8 

----编译无效对象

@?/rdbms/admin/utlrp.sql

----收集全库统计信息

begin
dbms_stats.gather_database_stats(
ESTIMATE_PERCENT=>DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt => 'for all indexed columns', cascade=>true, degree=>10);
end;
/
select owner,TABLE_NAME,NUM_ROWS,BLOCKS,LAST_ANALYZED from dba_tables order by LAST_ANALYZED desc;