Use expdp/impdp to import data from one database to another

Use expdp/impdp to import data from one database to another. 1. create DIRECTORY (the DIRECTORY must be created under the Database User) SQL code sqlplus system/manager create directory test_dir as '/home/orauat/zzj'; 2. Authorize SQL code Grant read, write on directory test_dir to cux; -- view the Directory and permission SQL code SELECT PRIVILEGE, DIRECTORY_NAME, DIRECTORY_PATH FROM USER_TAB_PRIVS T, ALL_DIRECTORIES D WHERE T. TABLE_NAME (+) = D. DIRECTORY_NAME order by 2, 1; 3. Execute the Export and Import SQL code su-orauat/orauat expdp cux/cux TABLES = export DUMPFILE = export DIRECTORY = test_dir LOGFILE = export impdp cux/cux DIRECTORY = test_dir DUMPFILE = export TABLE_EXISTS_ACTION = example of truncate logfile = cux_expdp_test_table.log: set the cux table in The UAT environment. data in cux_expdp_test_table is imported to the CRP2 environment. 1. Export data from The UAT environment: 1. create DIRECTORY (the DIRECTORY must be created under the Database User) SQL code login: orauat's Password :...... $ sqlplus system/manager SQL * Plus: Release 11.2.0.2.0 Production on Wed May 15 15:20:58 2013 Copyright (c) 1982,201 0, Oracle. all rights reserved. connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0-64bit Production With the Partitioning, OLAP, data Mining and Real Application Testing options SQL> create directory test_dir as '/home/orauat/zzj'; Directory created. SQL> Grant read, write on directory test_dir to cux; Grant succeeded. SQL> exit Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.2.0-64bit Production With the Partitioning, OLAP, data Mining and Real Application Testing options $ cd/home/orauat/zzj $ expdp cux/cux TABLES = cux_expdp_test_table DUMPFILE = cux_expdp_test_table.dmp DIRECTORY = test_dir LOGFILE = Export: release 11.2.0.2.0-Production on Wed May 15 15:06:12 2013 Copyright (c) 1982,200 9, Oracle and/or its affiliates. all rights reserved .;;; connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0-64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options Starting "CUX ". "SYS_EXPORT_TABLE_01": cux/******** TABLES = cux_expdp_test_table DUMPFILE = cux_expdp_test_table.dmp DIRECTORY = test_dir LOGFILE = descriestimate in progress using BLOCKS method... processing object type TABLE_EXPORT/TABLE/TABLE_DATA Total estimation using BLOCKS method: 128 KB Processing object type TABLE_EXPORT/TABLE .. exported "CUX ". "CUX_EXPDP_TEST_TABLE" 5.460 KB 5 rows Master table "CUX ". "SYS_EXPORT_TABLE_01" successfully loaded/unloaded ********************************* **************************************** * *** Dump file set for CUX. SYS_EXPORT_TABLE_01 is:/export/home/orauat/zzj/cux_expdp_test_table.dmp Job "CUX ". "SYS_EXPORT_TABLE_01" successfully completed at 15:07:02 $ ls cux_expdp_test_table.dmp cux_expdp_test_table.log $2. Download cux_expdp_test_table.dmp from the/home/orauat/zzj directory, then upload the data to/home/javasrp2/zzj in the CRP2 environment. 2. upload the data to the CRP2 environment. 1. create DIRECTORY (the DIRECTORY must be created under the Database User). SQL code login: unzip RP2 into RP2's Password :... $ sqlplus system/manager SQL * Plus: Release 11.2.0.2.0 Production on Wed May 15 15:20:58 2013 Copyright (c) 1982,201 0, Oracle. all rights reserved. connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0-64bit Production With the Partitioning, OLAP, data Mining and Real Application Testing options SQL> create directory test_dir as '/home/export RP2/zzj'; Directory created. SQL> Grant read, write on directory test_dir to cux; Grant succeeded. SQL> exit Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.2.0-64bit Production With the Partitioning, OLAP, data Mining and Real Application Testing options $ cd/home/export RP2/zzj $ ls restart $ impdp cux/cux DIRECTORY = test_dir DUMPFILE = cux_expdp_test_table.dmp TABLE_EXISTS_ACTION = truncate logfile = Export Import: release 11.2.0.2.0-Production on Wed May 15 15:21:55 2013 Copyright (c) 1982,200 9, Oracle and/or its affiliates. all rights reserved. connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0-64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options Master table "CUX ". "SYS_IMPORT_FULL_01" successfully loaded/unloaded Starting "CUX ". "SYS_IMPORT_FULL_01 ": cux/********* DIRECTORY = test_dir DUMPFILE = export TABLE_EXISTS_ACTION = truncate logfile = export Processing object type TABLE_EXPORT/TABLE/TABLE_DATA .. imported "CUX ". "CUX_EXPDP_TEST_TABLE" 5.460 KB 5 rows Job "CUX ". "SYS_IMPORT_FULL_01" successfully completed at 15:22:22 $ ls cux_expdp_test_table.dmp cux_expdp_test_table.log $