local_db installed in SuSE
LOCAL_DB's Tnsname
(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP) (HOST = 192.168.31.110) (PORT = 1521))
(Connect_data = (SERVER = dedicated) (service_name = ora11g)))
1. Built Dblink connected to 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. Grant HR data Pump export permissions
GRANT datapump_exp_full_database to HR;
3. Add the Tnsname of remote_db to the 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 installed in Win7
REMOTE_DB's Tnsname
win7 = (DESCRIPTION = (Address_list = (ADDRESS = (PROTOCOL = TCP) (HOST = 192.168.31.10) (PORT = 1521))
(Connect_data = (service_name = orcl.oracle.com)))
1. Built Dblink connected to 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. Build Catalogue Dmp_dir
sql> CREATE DIRECTORY dmp_dir as ' E:\DUMP ';
Sql> GRANT READ, WRITE on DIRECTORY Dmp_dir to Scott;
3. Grant Scott data Pump import permissions
GRANT Datapump_imp_full_database to Scott;
Import local_db's HR metadata into remote_db's dmp_dir
EXPDP scott/[email protected]win7 directory=dmp_dir nologfile=yes schemas=hr network_link=SUSE _api_link
[Email protected]:/> expdp scott/[email protected] 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, the 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 rows
: Exported" HR "." Departments "8.265 KB rows
: Exported" HR "." EMPLOYEES "19.95 KB 107 rows
: Exported" HR "." JOBS "8.218 KB rows
: Exported" HR "." Job_history "8.539 KB ten rows
: Exported" HR "." LOCATIONS "9.992 KB All 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
Data pump exports data locally to a remote database