数据泵使用NETWORK_LINK不落地导入数据

Oracle数据泵可以不用再本地先导出dump文件,然后scp到目标端,最后导入的过程。

可以使用NETWORK_LINK不需要真正的在操作系统层面导出dump文件,节省空间转换资源。

环境11.2.0.4 流程如下

  
1. 查看所有表空间对应的大小： 
select tablespace_name,sum(bytes)/1024/1024 from dba_data_files  group by tablespace_name;
查询数据库表空间的总大小：
SQL> select sum(bytes)/1024/1024 tablespace_name from dba_data_files;
TABLESPACE_NAME       2055.625
2.已经使用表空间大小：select tablespace_name,sum(bytes)/1024/1024 from dba_free_space group by tablespace_name;
1.查看RM用户下的（查看RM用户下的表）
select owner,segment_name,bytes/1024/1024 mb from dba_segments where owner=’RM’ order by 3 desc;
4.查看当前用户下的表：
 select table_name from user_tables;
5.查看该用户有权限查看的表空间
Select tablespace_name from user_tablespaces;
6、查询旧库表空间并在新库创建（这个在PL/SQL里面执行，会出现创建表空间语句，然后复制粘贴在新库执行就可以了）
select 'create bigfile tablespace  '||tablespace_name||' datafile size 100m autoextend on next 300m maxsize 34359738344K
 logging extent management local autoallocate blocksize 8K segment space management auto flashback on;'
 from (select distinct tablespace_name  from dba_segments where owner='RM');
7、创建临时表空间
create temporary tablespace RM_TEMP tempfile size 100m  autoextend on next 300m;
8、新库创建完表空间后创建用户
create user RM identified by rmlb2017 default tablespace RM_DATA temporary tablespace RM_TEMP;
9.创建dblink，用于不落地导入数据
 create public database link jcbk connect to rm identified by rm2017
 using '(DESCRIPTION=
 (ADDRESS_LIST=
 (ADDRESS=(PROTOCOL=TCP)(HOST=10.150.159.xx)(PORT=1521))
 )
 (CONNECT_DATA=
 (SERVICE_name=dbn)
 )
 )';
10.在新库执行导入语句
impdp rm/rm2017 DIRECTORY=impdp NETWORK_LINK=jcbk SCHEMAS=rm LOGFILE=rm.log 
 exclude=table:"IN('RECG_SUSP_RESULT_PHOTO')",table:"IN('VMC_ALARM_PIC')"
impdp rm/rm2017 DIRECTORY=impdp NETWORK_LINK=jcbk tables=VMC_ALARM_PIC LOGFILE=rmVMC_ALARM_PIC.log  
impdp rm/rm2017 DIRECTORY=impdp NETWORK_LINK=jcbk tables=RECG_SUSP_RESULT_PHOTO LOGFILE=RECG_SUSP_RESULT_PHOTO.log