Import Oracle dump in different tablespace

Follow below steps to Import Oracle dump into different Tablespace

  1. With your .DMP file, create a SQL file containing the structure (Tables):
    imp <username>/<password>@<sid> file=<filename.dmp> indexfile=index.sql full=y
  2. Open the indexfile (index.sql) in a text editor 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 account:
    sqlplus <username>/<password>@<sid> @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 <username>/<password>@<sid> file=<filename.dmp> fromuser=<from_username> touser=<to_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.

Leave a Reply

Your email address will not be published.