local_db 安裝在suse
local_db的tnsname
(DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.31.110)(PORT = 1521))
(CONNECT_DATA =(SERVER = DEDICATED)(SERVICE_NAME = ora11g)))
1.建dblink連線到remote_db
CREATE DATABASE LINK WIN7_API_LINK
CONNECT TO SCOTT IDENTIFIED BY tiger
USING '(DESCRIPTION =(ADDRESS_LIST =(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.31.10)(PORT = 1521)))
(CONNECT_DATA =(SERVICE_NAME = orcl.oracle.com)))';
2.授予hr資料泵匯出許可權
GRANT DATAPUMP_EXP_FULL_DATABASE TO HR;
3.將remote_db的tnsname新增到local_db的tnsname
win7=(DESCRIPTION =(ADDRESS_LIST =(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.31.10)(PORT = 1521)))
(CONNECT_DATA =(SERVICE_NAME = orcl.oracle.com)))
remote_db 安裝在win7
remote_db的tnsname
win7 =(DESCRIPTION =(ADDRESS_LIST =(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.31.10)(PORT = 1521)))
(CONNECT_DATA =(SERVICE_NAME = orcl.oracle.com)))
1.建dblink連線到local_db
CREATE DATABASE LINK SUSE_API_LINK
CONNECT TO HR IDENTIFIED BY hr
USING '(DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.31.110)(PORT = 1521))
(CONNECT_DATA =(SERVER = DEDICATED)(SERVICE_NAME = ora11g)))';
2.建目錄DMP_DIR
SQL> CREATE DIRECTORY DMP_DIR AS 'E:\DUMP';
SQL> GRANT READ, WRITE ON DIRECTORY DMP_DIR TO scott;
3.授予scott資料泵匯入許可權
GRANT DATAPUMP_IMP_FULL_DATABASE TO scott;
將local_db的hr後設資料匯入到remote_db的DMP_DIR中
expdp scott/tiger@win7 DIRECTORY=DMP_DIR NOLOGFILE=YES schemas=hr NETWORK_link=SUSE_API_LINK
oracle@suse-wwx:/> expdp scott/tiger@win7 DIRECTORY=DMP_DIR NOLOGFILE=YES schemas=hr NETWORK_link=SUSE_API_LINK
Export: Release 11.2.0.2.0 - Production on Sat Jan 3 23:11:35 2015
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
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 "SCOTT"."SYS_EXPORT_SCHEMA_01": scott/********@win7 DIRECTORY=DMP_DIR NOLOGFILE=YES schemas=hr NETWORK_link=SUSE_API_LINK
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 1.312 MB
Processing SCHEMA_EXPORT/USER
Processing SCHEMA_EXPORT/SYSTEM_GRANT
Processing SCHEMA_EXPORT/ROLE_GRANT
Processing SCHEMA_EXPORT/DEFAULT_ROLE
Processing SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing SCHEMA_EXPORT/DB_LINK
Processing SCHEMA_EXPORT/SEQUENCE/SEQUENCE
Processing SCHEMA_EXPORT/TABLE/TABLE
Processing SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing SCHEMA_EXPORT/TABLE/COMMENT
Processing SCHEMA_EXPORT/PROCEDURE/PROCEDURE
Processing SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
Processing SCHEMA_EXPORT/VIEW/VIEW
Processing SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing SCHEMA_EXPORT/TABLE/TRIGGER
Processing SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "HR"."E1" 69.17 KB 5 rows
. . exported "HR"."E2" 69.17 KB 5 rows
. . exported "HR"."E4" 69.17 KB 5 rows
. . exported "HR"."EXP_JOB3" 69.18 KB 5 rows
. . exported "HR"."EXP_JOB4" 69.18 KB 5 rows
. . exported "HR"."EXP_JOB5" 69.18 KB 5 rows
. . exported "HR"."EXP_JOB6" 69.18 KB 5 rows
. . exported "HR"."COUNTRIES" 7.179 KB 25 rows
. . exported "HR"."DEPARTMENTS" 8.265 KB 27 rows
. . exported "HR"."EMPLOYEES" 19.95 KB 107 rows
. . exported "HR"."JOBS" 8.218 KB 19 rows
. . exported "HR"."JOB_HISTORY" 8.539 KB 10 rows
. . exported "HR"."LOCATIONS" 9.992 KB 23 rows
. . exported "HR"."REGIONS" 6.125 KB 4 rows
. . exported "HR"."EMP" 0 KB 0 rows
. . exported "HR"."WX_EMP" 0 KB 0 rows
Master table "SCOTT"."SYS_EXPORT_SCHEMA_01"
******************************************************************************
Dump file set for SCOTT.SYS_EXPORT_SCHEMA_01 is:
E:\DUMP\EXPDAT.DMP
Job "SCOTT"."SYS_EXPORT_SCHEMA_01" successfully completed at 23:15:03