Howto import an oracle dump in an different tablespace

If you're using Oracle 10g and datapump, you can use the REMAP_TABLESPACE clause. example:

REMAP_TABLESPACE=A_TBLSPACE:NEW_TABLESPACE_GOES_HERE

You've got a couple of issues here.

Firstly, the different versions of Oracle you're using is the reason for the table statistics error - I had the same issue when some of our Oracle 10g Databases got upgraded to Release 2, and some were still on Release 1 and I was swapping .DMP files between them.

The solution that worked for me was to use the same version of exp and imp tools to do the exporting and importing on the different Database instances. This was easiest to do by using the same PC (or Oracle Server) to issue all of the exporting and importing commands.

Secondly, I suspect you're getting the ORA-00959: tablespace 'A_TBLSPACE' does not exist because you're trying to import a .DMP file from a full-blown Oracle Database into the 10g Express Edition (XE) Database, which, by default, creates a single, predefined tablespace called USERS for you.

If that's the case, then you'll need to do the following..

1. With your .DMP file, create a SQL file containing the structure (Tables):

   imp <xe_username>/<password>@XE file=<filename.dmp> indexfile=index.sql full=y

2. Open the indexfile (index.sql) in a text editor that can do find and replace over an entire file, and issue the following find and replace statements IN ORDER (ignore the single quotes.. '):

   Find: 'REM<space>' Replace: <nothing>

   Find: '"<source_tablespace>"' Replace: '"USERS"'

   Find: '...' Replace: 'REM ...'

   Find: 'CONNECT' Replace: 'REM CONNECT'

3. Save the indexfile, then run it against your Oracle Express Edition account (I find it's best to create a new, blank XE user account - or drop and recreate if I'm refreshing):

   sqlplus <xe_username>/<password>@XE @index.sql

4. Finally run the same .DMP file you created the indexfile with against the same account to import the data, stored procedures, views etc:

   imp <xe_username>/<password>@XE file=<filename.dmp> fromuser=<original_username> touser=<xe_username> ignore=y

You may get pages of Oracle errors when trying to create certain objects such as Database Jobs as Oracle will try to use the same Database Identifier, which will most likely fail as you're on a different Database.