DB Importing with Different Directory Structure

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.

Thanks for your helpful information!  Yes, that is what I need the full database import.

yes, I have the same name for tablespace.

With a the old exp/imp method, even with full, that SYS grants are not exported and have to be captured.

they can be captured in SQL

SELECT DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT','USER1') FROM DUAL;

SELECT DBMS_METADATA.GET_GRANTED_DDL('OBJECT_GRANT','USER1') FROM DUAL;

SYSTEM_GRANT is an include object which can be used with datapump.