Oracle DMP file Import (restore) to different table spaces and different users

Oracle dmp file Import (restore) to different table spaces and different users

First, Exp/imp solution:

1, can use SED to modify the DMP file, normal import

Sed-i ' s/test_ts/users/g ' a.dmp

2. The DMP file contains information about the table space when it was exported, and the table space does not exist if the corresponding tablespace is not created when importing
3. If several tables are exported, errors will be ignored when importing ignore=y.

4, with the Transport_tablespace parameter, transport tablespace move data under one tablespace to another table space

4.1. Export data using transport_tablespace EXP user name Transport_tablespace=y tablespaces= (test_ts) file=/opt/b.dmp exp test1 file=t EST1.DMP (export without transport_tablespace parameter) 4.2. Move data to test1 under the same user on another server imp test1 transport_tablespace=y file=a.dmp DATAFILES=/U01/APP/ORACLE/ORADATA/GBKSL/A01.DBF Ignore=y full=y;4.3, on another server, the Test1 export DMP file data is moved to TEST2 user imp test1 TR Ansport_tablespace=y file=/opt/test1.dmp fromuser=test1 touser=test2 datafiles= '/u01/app/oracle/oradata/gbksl/ A01.DBF ' Ignore=y full=y;

Second, IMPDP solution:

Import using the IMPDP command

IMPDP test1/test123 dumpfile=xxx.dmp directory=dpdata1 remap_schema=testl:test2 Remap_tablespace=ts_test1_prod: Test2_data

Parameter description

DumpFile: The DMP file name that needs to be imported

Directory folder where the Directory:dmp file resides

Remap_schema: This option is used to load all objects of the source scheme into the target scenario. Remap_schema=source_schema:target_schema

Remap_tablespace: Imports all objects from the source table space into the target table space. Remap_tablespace=source_tablespace:target:tablespace

Where: Ts_test1_prod: The table space name of the original DMP file

Test2_data: Name of the tablespace to import into

Oracle DMP file Import (restore) to different table spaces and different users