Database Tutorials MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux 
We can import an object from the database to a different database and a different tablespace with a different schema.
1. First, let’s get this done using the data transfer method.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 | $ impdp directory=data_pump_dir network_link=cclink tables=EMRAH.MUSTERILER remap_schema=EMRAH:EMRAH remap_tablespace=DENEME:USERS logfile=remap.log; Import: Release 11.2.0.1.0 - Production on Mon Nov 3 18:17:06 2014 Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved. Username: sys as sysdba Password: Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production With the Partitioning, Automatic Storage Management, OLAP, Data Mining and Real Application Testing options Starting "SYS"."SYS_IMPORT_TABLE_01": sys/******** AS SYSDBA directory=data_pump_dir network_link=cclink tables=EMRAH.MUSTERILER remap_schema=EMRAH:EMRAH remap_tablespace=DENEME:USERS logfile=remap.log Estimate in progress using BLOCKS method... Processing object type TABLE_EXPORT/TABLE/TABLE_DATA Total estimation using BLOCKS method: 12 MB Processing object type TABLE_EXPORT/TABLE/TABLE . . imported "EMRAH"."MUSTERILER" 55500 rows Job "SYS"."SYS_IMPORT_TABLE_01" successfully completed at 18:17:23 |
If we question the result.
1 2 3 4 5 | SQL> select OWNER, TABLESPACE_NAME from dba_tables where table_name='MUSTERILER'; OWNER TABLESPACE_NAME ------------------------------ ------------------------------ EMRAH USERS |
2. We can export the table and export it to a different schema and tablespace.
First, let’s “export” a table from the database named “cc”.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 | $ expdp directory=expimp_dump tables=EMRAH.ULKELER dumpfile=ulke.dmp logfile=ulke.log Export: Release 11.2.0.4.0 - Production on Mon Nov 3 18:53:36 2014 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. Username: sys as sysdba Password: Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, Oracle Label Security, OLAP, Data Mining, Oracle Database Vault and Real Application Testing options Starting "SYS"."SYS_EXPORT_TABLE_01": sys/******** AS SYSDBA directory=expimp_dump tables=EMRAH.ULKELER dumpfile=ulke.dmp logfile=ulke.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 "EMRAH"."ULKELER" 6.281 KB 25 rows Master table "SYS"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded ****************************************************************************** Dump file set for SYS.SYS_EXPORT_TABLE_01 is: /u01/app/oracle/admin/cc/expimp_dir/ulke.dmp Job "SYS"."SYS_EXPORT_TABLE_01" successfully completed at Mon Nov 3 18:53:52 2014 elapsed 0 00:00:07 |
Then we send the “DUMP” file to the data_dump_dir folder of the database named “catalog” with scp.
1 2 3 4 | $ scp /u01/app/oracle/admin/cc/expimp_dir/ulke.dmp oracle@172.20.31.232:/u01/app/oracle/admin/catalog/dpdump/ oracle@172.20.31.232's password: ulke.dmp 100% 92KB 92.0KB/s 00:00 |
Let’s “import” to our database named “catalog”.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 | $ impdp directory=data_pump_dir dumpfile=ulke.dmp remap_schema=EMRAH:MELIH remap_tablespace=DENEME:DENEME; Import: Release 11.2.0.1.0 - Production on Mon Nov 3 18:47:15 2014 Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved. Username: sys as sysdba Password: Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production With the Partitioning, Automatic Storage Management, OLAP, Data Mining and Real Application Testing options Master table "SYS"."SYS_IMPORT_FULL_01" successfully loaded/unloaded Starting "SYS"."SYS_IMPORT_FULL_01": sys/******** AS SYSDBA directory=data_pump_dir dumpfile=ulke.dmp remap_schema=EMRAH:MELIH remap_tablespace=DENEME:DENEME Processing object type TABLE_EXPORT/TABLE/TABLE Processing object type TABLE_EXPORT/TABLE/TABLE_DATA . . imported "MELIH"."ULKELER" 6.281 KB 25 rows Job "SYS"."SYS_IMPORT_FULL_01" successfully completed at 18:47:28 |
