本文共 1809 字，大约阅读时间需要 6 分钟。
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.6252.已经使用表空间大小：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
转载地址：http://avlyz.baihongyu.com/