Use a Data Pump to export remote data to a local database

The remote database user test has a t table.

1. SQL> showUser
2. USERIs"TEST"
3. SQL>Select*FromTab;
5. TNAME TABTYPE CLUSTERID
6. -----------------------------------------------
7. TTABLE
9. SQL>! Cat/etc/hosts
10. # DoNotRemove the following line,OrVarious programs
11. # That require network functionality will fail.
12. 127.0.0.1 linux localhost. localdomain localhost
13. : 1 localhost6.localdomain6 localhost6
14. 10.10.10.7 linux localhost. localdomain localhost

The local database user ing does not have a t table.

1. SQL> showUser
2. USERIs"ING"
3. SQL>Select*FromTabWhereTname ='T';
5. Unselected row
7. SQL>! Cat/etc/hosts
8. # DoNotRemove the following line,OrVarious programs
9. # That require network functionality will fail.
10. 127.0.0.1 linux localhost. localdomain localhost
11. : 1 localhost6.localdomain6 localhost6
12. 10.10.10.8 linux localhost. localdomain localhost
13. 10.10.10.8 linux
14. 10.10.10.8 localhost

Now, we need to import all objects under user test to user ing.

1. Create a database link object and link it to the database server of 10.10.10.7.

1. SQL> conn system/Oracle
2. Connected.
3. SQL> showUser
4. USERIs"SYSTEM"
5. SQL>Create DatabaseLink link7Connect ToTest identifiedByTest using'10. 10.10.7/orcl';
7. The database link has been created.
9. SQL>Select Count(*)FromTest. t @ link7;
11. COUNT(*)
12. ----------
13. 50027

2. the user test of the Remote Data 10.10.10.7 must have the exp_full_database permission.

1. SQL> conn/AsSysdba
2. Connected.
3. SQL> showUser
4. USERIs"SYS"
5. SQL>GrantExp_full_databaseToTest;
7. Authorization successful.

3. Execute export on the 10.10.10.8 server.

1. [Oracle @ linux exp] $ expdp system/oracle @ orcl network_link = link7 directory = exp schemas = test
3. Export: Release 10.2.0.4.0-ProductionOnWednesday, October, 2011 19:48:57
5. Copyright (c) 2003,200 7, Oracle.AllRights reserved.
7. Connect to: OracleDatabase10g Enterprise Edition Release 10.2.0.4.0-Production
8. WithThe Partitioning, OLAP, Data MiningAnd RealApplication Testing options
9. Start"SYSTEM"."SYS_EXPORT_SCHEMA_01": System/******** @ orcl network_link = link7 directory = exp schemas = test
10. Using the BLOCKS Method for estimation...
11. Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
12. Total estimation using the BLOCKS method: 6 MB
13. Processing object type SCHEMA_EXPORT/USER
14. Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
15. Processing object type SCHEMA_EXPORT/ROLE_GRANT
16. Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
17. Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
18. Processing object type SCHEMA_EXPORT/TABLE/TABLE
19. .. Exported"TEST"."T"4.667 MB 50027 rows
20. The master table is successfully loaded/uninstalled."SYSTEM"."SYS_EXPORT_SCHEMA_01"
21. **************************************** **************************************
22. The dump file set of SYSTEM. SYS_EXPORT_SCHEMA_01 is:
23. /U01/exp/expdat. dmp
24. Job"SYSTEM"."SYS_EXPORT_SCHEMA_01"Completed successfully at 19:50:10

4. Run the import command on the 10.10.10.8 server.

1. [Oracle @ linux exp] $ impdp system/oracle @ orcl directory = exp dumpfile = expdat. dmp remap_schema = test: ing
3. Import: Release 10.2.0.4.0-ProductionOnWednesday, October, 2011 19:51:24
5. Copyright (c) 2003,200 7, Oracle.AllRights reserved.
7. Connect to: OracleDatabase10g Enterprise Edition Release 10.2.0.4.0-Production
8. WithThe Partitioning, OLAP, Data MiningAnd RealApplication Testing options
9. The master table is successfully loaded/uninstalled."SYSTEM"."SYS_IMPORT_FULL_01"
10. Start"SYSTEM"."SYS_IMPORT_FULL_01": System/******** @ orcl directory = exp dumpfile = expdat. dmp remap_schema = test: ing
11. Processing object type SCHEMA_EXPORT/USER
12. ORA-31684: object type.USER:"ING"Already exists-- This user is not automatically created here.
13. Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
14. Processing object type SCHEMA_EXPORT/ROLE_GRANT
15. Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
16. Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
17. Processing object type SCHEMA_EXPORT/TABLE/TABLE
18. Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
19. .. Imported"ING"."T"4.667 MB 50027 rows
20. Job"SYSTEM"."SYS_IMPORT_FULL_01"Completed, but there is an error (completed at 19:51:28)

5. Check the user ing on 10.10.10.8.

1. SQL> conn ing/ing
2. Connected.
3. SQL> showUser
4. USERIs"ING"
5. SQL>Select*FromTabWhereTname ='T';
7. TNAME TABTYPE CLUSTERID
8. -----------------------------------------------
9. TTABLE
11. SQL>Select Count(*)FromT;
13. COUNT(*)
14. ----------
15. 50027

Note: The imported users have unlimited power to use the table space: alter user ing quota unlimited on users; the system users here certainly have this power. This can be done for a small database because it depends on the network. For large databases, export data from the data source and copy the data to the target database for import.