源库：192.168.162.200 oracle11g
目标库：192.168.162.199 node01
1.源端创建测试数据
[oracle@oracle11g admin]$ sqlplus test/oracle
SQL*Plus: Release 11.2.0.4.0 Production on Tue Jul 5 02:33:28 2022
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> create table tt (id number(10));
Table created.
SQL> insert into tt values (1);
1 row created.
SQL> insert into tt values (2);
1 row created.
SQL> commit;
Commit complete.
SQL> select * from tt;
ID
----------
1
2
2.目标库tnsnames.ora添加以下内容
ORCL2 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.162.200)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = ORCL)
)
)
测试：
[oracle@node01 admin]$ tnsping orcl2
TNS Ping Utility for Linux: Version 11.2.0.4.0 - Production on 15-JUL-2022 07:07:29
Copyright (c) 1997, 2013, Oracle. All rights reserved.
Used parameter files:
/u01/app/oracle/product/11.2.0/db_1/network/admin/sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.162.200)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = ORCL)))
OK (10 msec)
3.创建dblink
SQL> create public database link dblk_system connect to system identified by oracle using 'orcl2';
Database link created.
测试：
SQL> select * from dual@dblk_system;
DU
--
X
4.创建directory并授权
SQL> create or replace directory dmpdir as '/home/oracle';
Directory created.
SQL> grant read,write on directory dmpdir to system;
Grant succeeded.
5.目标端创建test用户
SQL> create user test identified by oracle account unlock;
User created.
SQL> grant connect,resource to test;
Grant succeeded.
6.测试network_link方式在目标端导入数据
[oracle@node01 admin]$ impdp system/oracle directory=dmpdir tables=test.tt remap_schema=test:test remap_tablespace=user:user network_link=dblk_system logfile=impdp_test_tt_system.log
Import: Release 11.2.0.4.0 - Production on Fri Jul 15 07:23:37 2022
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
UDI-28002: operation generated ORACLE error 28002
ORA-28002: the password will expire within 7 days
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYSTEM"."SYS_IMPORT_TABLE_01": system/******** directory=dmpdir tables=test.tt remap_schema=test:test remap_tablespace=user:user network_link=dblk_system logfile=impdp_test_tt_system.log
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 64 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
. . imported "TEST"."TT" 2 rows
Job "SYSTEM"."SYS_IMPORT_TABLE_01" successfully completed at Fri Jul 15 07:23:44 2022 elapsed 0 00:00:06
查询导入后结果：
SQL> select * from test.tt;
ID
----------
1
2
7.测试network_link方式导出数据
[oracle@node01 admin]$ expdp system/oracle directory=dmpdir dumpfile=test_emp.dmp tables=test.emp network_link=dblk_system logfile=test_emp.log
Export: Release 11.2.0.4.0 - Production on Fri Jul 15 07:14:10 2022
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
UDE-28002: operation generated ORACLE error 28002
ORA-28002: the password will expire within 7 days
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYSTEM"."SYS_EXPORT_TABLE_01": system/******** directory=dmpdir dumpfile=test_emp.dmp tables=test.emp network_link=dblk_system logfile=test_emp.log
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 64 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
. . exported "TEST"."EMP" 8.562 KB 14 rows
Master table "SYSTEM"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_TABLE_01 is:
/home/oracle/test_emp.dmp
Job "SYSTEM"."SYS_EXPORT_TABLE_01" successfully completed at Fri Jul 15 07:14:16 2022 elapsed 0 00:00:06
