oracle 跨服务器 导表,impdp跨服务器导数据

源服务器：192.168.0.40

不需要做任何操作,只需要知道数据账号，保持2边字符集一致，注意2边表空间名

目标服务器:192.168.0.30

做如下操作

一、在目标服务器上添加tnsname

在/opt/app/oracle/product/10.2.0/db_1/network/admin/tnsnames.ora配置文件追加如下

remount =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.40)(PORT = 1521))

)

(CONNECT_DATA =

(SID = remount)

)

)

二、以普通用户登陆目标服务器，创建dblink设备；

CREATE public DATABASE LINK dmplinkremount CONNECT TO  remountuser IDENTIFIED BY passwd321 USING 'remount';

三、以管理身份登陆目标服务器，创建directory对象

$ sqlplus / as sysdba

sql> create or replace directory dmpdir as '/opt/app/oracle/dmp';

sql> grant read,write on directory dmpdir to public;

sql> exit;

四、查询2边数据库；看用户那些表空间需要迁移

2边分别执行如下sql；对比表空间

select t.tablespace_name, round(sum(bytes/(1024*1024)),0) ts_size

from dba_tablespaces t, dba_data_files d

where t.tablespace_name = d.tablespace_name

group by t.tablespace_name;

五、执行导数据操作

impdp localuser/localuserpass directory=dmpdir network_link=dmplinkremount  REMAP_SCHEMA=remountuser:localuser REMAP_TABLESPACE=remount01:localuserspace,INDEXS:localuserspace,EXAMPLE:localuserspace,PERFSTAT:localuserspace logfile=20121031xx.log

参考文档:

http://space.itpub.net/16860121/viewspace-630043