数据泵使用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