In case I have 2 Oracle database DB1 and DB2, and the directory structure of the DB2 environment is different from DB1, can I import a full database which is exported from DB1 to DB2?
With import the DESTROY parameter is set to N by default. When set to Y overwrite tablespace data files.
If you precreate the tablespaces inDB2 using whatever directory structure you require for the datafiles and import with DESTROY=N, the users and segments will be placed in the correct tablespaces,
That being said, you can do a full import but is that what you need? A full import is using a hammer to turn a screw and generally lacks the level of control that an Oracle DBA requires. There are various methods available using the old exp/imp and the newer datapump exp/imp tools. If you use Oracle datapump export and import then you can remap your users and tablespaces as required for full or partial exports. For example using expdp/impdp to copy a schema between databases
Do a Schema export from DB1
$ expdp ‘\ as sysdba’ DIRECTORY=my_dir DUMPFILE=expdp.dmp LOGFILE=expdp.log SCHEMAS=user1
Then import objects into schema into a remapped schema and tablespaces on DB2
$ impdp ‘\ as sysdba’ DIRECTORY=my_dir DUMPFILE=expdp.dmp LOGFILE=impdp.log REMAP_SCHEMA=user1:user2 REMAP_TABLESPACE=DATA1:DATA2
REMAP_TABLESPACE=INDEX1:INDEX2
or using older exp/imp methods
Take full “no rows” export (parameter full=y rows=0). This is excellent for extracting and manipulating all DDL (users,tablespaces and objects (tables,indexes,packages,views etc)) using a tool such as DDLWizard (freeware: http://www.ddlwizard.com/dt_download.htm).
You then use a simplified data export to import in the required data.
dba_hba
63 Posts
1163
1
Posted June 20th, 2012 04:00
With import the DESTROY parameter is set to N by default. When set to Y overwrite tablespace data files.
If you precreate the tablespaces inDB2 using whatever directory structure you require for the datafiles and import with DESTROY=N, the users and segments will be placed in the correct tablespaces,
That being said, you can do a full import but is that what you need? A full import is using a hammer to turn a screw and generally lacks the level of control that an Oracle DBA requires. There are various methods available using the old exp/imp and the newer datapump exp/imp tools. If you use Oracle datapump export and import then you can remap your users and tablespaces as required for full or partial exports. For example using expdp/impdp to copy a schema between databases
Do a Schema export from DB1
$ expdp ‘\ as sysdba’ DIRECTORY=my_dir DUMPFILE=expdp.dmp LOGFILE=expdp.log SCHEMAS=user1
Then import objects into schema into a remapped schema and tablespaces on DB2
$ impdp ‘\ as sysdba’ DIRECTORY=my_dir DUMPFILE=expdp.dmp LOGFILE=impdp.log REMAP_SCHEMA=user1:user2 REMAP_TABLESPACE=DATA1:DATA2
REMAP_TABLESPACE=INDEX1:INDEX2
or using older exp/imp methods
Take full “no rows” export (parameter full=y rows=0). This is excellent for extracting and manipulating all DDL (users,tablespaces and objects (tables,indexes,packages,views etc)) using a tool such as DDLWizard (freeware: http://www.ddlwizard.com/dt_download.htm).
You then use a simplified data export to import in the required data.