Oracle跳过字段导入数据,使用impdp network link 跳过expdp直接导入数据

源数据库：rman01

目标数据库: rman02

实验步骤：

1. 配置源数据库与目标数据库的tnsnames.ora信息

2. 在源数据库创建测试表，创建public db link

3. 在目标库使用 imp network_link 参数  直接导入schema

4. 查看测试结果

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

1. 配置源数据库与目标数据库的tnsnames.ora信息

源数据库tnsname.ora配置信息如下:

[oracle@localhost admin]$ more tnsnames.ora

# tnsnames.ora Network Configuration File:

/data/oracle/product/11.2.0/db_1/network/admin/tnsnames.ora

# Generated by Oracle configuration tools.

RMAN02 =

(DESCRIPTION =

(ADDRESS =

(PROTOCOL = TCP)(HOST = 192.168.248.129)(PORT = 1521))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = rman02)

) )

RMAN01 =

(DESCRIPTION =

(ADDRESS =

(PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = rman01)

)

)

目标数据库 tnsnames.ora配置信息如下：

[oracle@localhost admin]$ more tnsnames.ora

# tnsnames.ora Network Configuration File:

/data/oracle/product/11.2.0/db_1/network/admin/tnsnames.ora

# Generated by Oracle configuration tools.

RMAN01 =

(DESCRIPTION =

(ADDRESS =

(PROTOCOL = TCP)(HOST = 192.168.248.132)(PORT = 1521))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = rman01)

)

)

RMAN02 =

(DESCRIPTION =

(ADDRESS =

(PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = rman02)

)

)

2. 在源数据库创建测试表，创建public db link

SQL> conn user01/gaoxu

Connected.

SQL> select * from tab;

TNAME TABTYPE CLUSTERID

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

TEST TABLE

SQL> create public database link rman02_to_rman01 connect to

system identified by gaoxu using 'RMAN01';

Database link created

3. 在目标库使用 imp network_link 参数  直接导入schema

[oracle@localhost dpdump]$ echo "/as sysdba" | impdp

directory=EXP_PUMP_DIR

logfile=rman02_to_rman01.log network_link=rman02_to_rman01 schemas=user01

remap_schema=user01:user02

Import: Release 11.2.0.1.0 - Production on Wed Jul 30 20:50:05

2014

Copyright (c) 1982, 2009, Oracle and/or its affiliates.

All rights reserved.

Username:

Connected to: Oracle Database 11g Enterprise Edition Release

11.2.0.1.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application

Testing options

Starting "SYS"."SYS_IMPORT_SCHEMA_01":  /********

AS SYSDBA directory=EXP_PUMP_DIR logfile=rman02_to_rman01.log

network_link=rman02_to_rman01 schemas=user01

remap_schema=user01:user02

Estimate in progress using BLOCKS method...

Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA

Total estimation using BLOCKS method: 64 KB

Processing object type SCHEMA_EXPORT/USER

Processing object type SCHEMA_EXPORT/SYSTEM_GRANT

Processing object type SCHEMA_EXPORT/ROLE_GRANT

Processing object type SCHEMA_EXPORT/DEFAULT_ROLE

Processing object type

SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA

Processing object type SCHEMA_EXPORT/TABLE/TABLE

. . imported "USER02"."TEST"  37 rows

Job "SYS"."SYS_IMPORT_SCHEMA_01" successfully completed at

20:52:23

4. 查看测试结果

SQL> conn user02/gaoxu

Connected.

SQL> select * from tab;

TNAME TABTYPE CLUSTERID

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

TEST TABLE

SQL> select count(*) from test;

COUNT(*)

----------

37