One of the expdp and impdp uses (different users and no tablespaces)
1. Create a DIRECTORY
SQL > create or replace directory dir_dp as'/ fol/dir_dp';Directory created.
2. Authorization
SQL > grant read,write on directory dir_dp to scott;Grant succeeded.
3. View directories and permissions
SQL > set lines 200pagesize 1000SQL > 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 PRIVILEGE DIRECTORY_NAME DIRECTORY_PATH -READ DATA_FILE_DIR / fol/app/oracle/product/11.2.0/db_1/demo/schema/sales_history/READ DATA_PUMP_DIR / fol/app/oracle/admin / CPP/dpdump/READ DATA_PUMP_DIR / fol/app/oracle/admin/CPP/dpdump/WRITE DATA_PUMP_DIR / fol/app/oracle/admin/CPP/dpdump/WRITE DATA_PUMP_DIR / fol/app/oracle/admin/CPP/dpdump/READ DIR_DP / fol/dir_dpWRITE DIR_DP / fol/dir_dpREAD LOG_FILE_DIR / fol/app/oracle/product/11.2.0/db_1/demo/schema/log/WRITE LOG_FILE_DIR / fol/app/oracle/product/11.2.0/db_1/ Demo/schema/log/READ MEDIA_DIR / fol/app/oracle/product/11.2.0/db_1/demo/schema/product_media/READ ORACLE_OCM_CONFIG_DIR / fol/app/oracle/product/11.2.0/db_1/ccr/stateWRITE ORACLE_OCM_CONFIG_DIR / fol/app/oracle/product/11 .2.0 / db_1/ccr/stateREAD ORACLE_OCM_CONFIG_DIR2 / fol/app/oracle/product/11.2.0/db_1/ccr/stateWRITE ORACLE_OCM_CONFIG_DIR2 / fol/app/oracle/product/11.2.0/db_1/ccr/stateREAD SS_OE_XMLDIR / fol/app/oracle/product/11.2. 0/db_1/demo/schema/order_entry/WRITE SS_OE_XMLDIR / fol/app/oracle/product/11.2.0/db_1/demo/schema/order_entry/READ SUBDIR / fol/app/oracle/product/11.2.0/db_1/demo/schema/order_entry//2002/SepWRITE SUBDIR / fol/app/oracle/product/11.2.0/db_1/demo/schema/order_entry//2002/Sep XMLDIR / fol/app/oracle/product/11.2.0/db_1/rdbms/xml19 rows selected.SQL > select DEFAULT_TABLESPACE from dba_users where username='SCOTT' DEFAULT_TABLESPACE--USERS
4. Perform export
Expdp scott/scott@CPP schemas=scott directory=dir_dp dumpfile = expdp_scott1.dmp logfile=expdp_scott1.log;$ expdp scott/scott@CPP schemas=scott directory=dir_dp dumpfile = expdp_scott1.dmp logfile=expdp_scott1.log;Export: Release 11.2.0.4.0-Production on Fri Sep 11 16:02:49 2015Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0-64bit ProductionWith the Partitioning, OLAP Data Mining and Real Application Testing optionsStarting "SCOTT". "SYS_EXPORT_SCHEMA_01": scott/*@CPP schemas=scott directory=dir_dp dumpfile=expdp_scott1.dmp logfile=expdp_scott1.log Estimate in progress using BLOCKS method...Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATATotal estimation using BLOCKS method: 10.18 MBProcessing object type SCHEMA_EXPORT/USERProcessing object type SCHEMA_EXPORT/SYSTEM_GRANTProcessing object type SCHEMA_EXPORT/ROLE_GRANTProcessing object type SCHEMA_EXPORT/DEFAULT_ROLEProcessing object type SCHEMA_EXPORT/PRE_ SCHEMA/PROCACT_SCHEMAProcessing object type SCHEMA_EXPORT/TABLE/TABLEProcessing object type SCHEMA_EXPORT/TABLE/COMMENTProcessing object type SCHEMA_EXPORT/TABLE/INDEX/INDEXProcessing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINTProcessing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICSProcessing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINTProcessing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS. . Exported "SCOTT". "TEST" 8.414 MB 86785 rows. . Exported "SCOTT". "DEPT" 5.937 KB 4 rows. . Exported "SCOTT". "EMP" 8.570 KB 14 rows. . Exported "SCOTT". "SALGRADE" 5.867 KB 5 rows. . Exported "SCOTT". "BONUS" 0 KB 0 rowsMaster table "SCOTT". "SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded**** * Dump file set for SCOTT.SYS_EXPORT_SCHEMA_01 is: / fol/dir_dp/expdp_scott1.dmpJob "SCOTT". "SYS_EXPORT_SCHEMA_01" successfully completed at Fri Sep 11 16:03:28 2015 elapsed 0 00:00:37
Target Library:
1. Create test tablespaces and users
SQL > create tablespace LLC datafile'+ DATA/phub/datafile/LLC01.dbf' size 5G Tablespace created.SQL > create user lilc identified by lilc default tablespace LLC;User created.SQL > grant dba to lilc;Grant succeeded.SQL > conn lilc/lilc;Connected.SQL > select DEFAULT_TABLESPACE from dba_users where username='LILC';DEFAULT_TABLESPACE--LLC
two。 Create DIRECTORY
SQL > create or replace directory dir_dp as'/ home/oracle/dir_dp';Directory created.
3. Authorization
SQL > grant read,write on directory dir_dp to lilc
4. Perform the import:
Replace tablespaces and replace user imports:
[oracle@cwogg dir_dp] $impdp lilc/lilc directory=dir_dp DUMPFILE=expdp_scott1.dmp remap_schema=scott:lilc remap_tablespace=USERS:LLC exclude=USERImport: Release 11.2.0.4.0-Production on Fri Sep 11 16:40:57 2015Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0-64bit ProductionWith the Partitioning, Automatic Storage Management, OLAP Data Miningand Real Application Testing optionsMaster table "LILC". "SYS_IMPORT_FULL_01" successfully loaded/unloadedStarting "LILC". "SYS_IMPORT_FULL_01": lilc/* directory=dir_dp DUMPFILE=expdp_scott1.dmp remap_schema=scott:lilc remap_tablespace=USERS:LLC exclude=USER Processing object type SCHEMA_EXPORT/SYSTEM_GRANTProcessing object type SCHEMA_EXPORT/ROLE_GRANTProcessing object type SCHEMA_EXPORT/DEFAULT_ROLEProcessing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMAProcessing object type SCHEMA_EXPORT/TABLE/TABLEProcessing object type SCHEMA_EXPORT/TABLE/TABLE_DATA. . Imported "LILC". "TEST" 16.81 MB 173570 rows. . Imported "LILC". "DEPT" 5.937 KB 4 rows. . Imported "LILC". "EMP" 8.570 KB 14 rows. . Imported "LILC". "SALGRADE" 5.867 KB 5 rows. . Imported "LILC". "BONUS" 0 KB 0 rowsProcessing object type SCHEMA_EXPORT/TABLE/INDEX/INDEXProcessing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINTProcessing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICSProcessing object type SCHEMA_EXPORT/VIEW/VIEWProcessing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINTProcessing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICSJob "LILC". "SYS_IMPORT_FULL_01" successfully completed at Fri Sep 11 16:41:05 2015 elapsed 0 00:00:07
There are no identical user exports and imports on the target library:
Expdp system/123123@CPP schemas=test directory=dir_dp dumpfile = test.dmp logfile=test.log;Export: Release 11.2.0.4.0-Production on Fri Sep 11 17:32:57 2015Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0-64bit ProductionWith the Partitioning, OLAP Data Mining and Real Application Testing optionsStarting "SYSTEM". "SYS_EXPORT_SCHEMA_01": system/*@CPP schemas=test directory=dir_dp dumpfile=test.dmp logfile=test.log Estimate in progress using BLOCKS method...Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATATotal estimation using BLOCKS method: 20.06MBProcessing object type SCHEMA_EXPORT/USERProcessing object type SCHEMA_EXPORT/SYSTEM_GRANTProcessing object type SCHEMA_EXPORT/ROLE_GRANTProcessing object type SCHEMA_EXPORT/DEFAULT_ROLEProcessing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMAProcessing object type SCHEMA_EXPORT/TABLE/TABLE. . Exported "TEST". "T" 16.81 MB 173576 rows. . Exported "TEST". "T2" 19.25 KB 74 rowsMaster table "SYSTEM". "SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded**** * Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is: / fol/dir_dp/test.dmpJob "SYSTEM". "SYS_EXPORT_SCHEMA_01" successfully completed at Fri Sep 11 17:33:06 2015 elapsed 0 00:00:07
Import: (change the user's default tablespace)
[oracle@cwogg dir_dp] $impdp lilc/lilc directory=dir_dp DUMPFILE=test.dmpremap_schema=test:test remap_tablespace=test:LLC logfile=test.log;Import: Release 11.2.0.4.0-Production on Fri Sep 11 17:53:26 2015Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0-64bit ProductionWith the Partitioning, Automatic Storage Management, OLAP Data Miningand Real Application Testing optionsMaster table "LILC". "SYS_IMPORT_FULL_01" successfully loaded/unloadedStarting "LILC". "SYS_IMPORT_FULL_01": lilc/* directory=dir_dp DUMPFILE=test.dmp remap_schema=test:test remap_tablespace=test:LLC logfile=test.log Processing object type SCHEMA_EXPORT/USERProcessing object type SCHEMA_EXPORT/SYSTEM_GRANTProcessing object type SCHEMA_EXPORT/ROLE_GRANTProcessing object type SCHEMA_EXPORT/DEFAULT_ROLEProcessing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMAProcessing object type SCHEMA_ EXPORT/TABLE/TABLEProcessing object type SCHEMA_EXPORT/TABLE/TABLE_DATA. . Imported "TEST". "T" 16.81 MB 173576 rows. . Imported "TEST". "T2" 19.25 KB 74 rowsJob "LILC". "SYS_IMPORT_FULL_01" successfully completed at Fri Sep 11 17:53:32 2015 elapsed 0 00:00:05
