Problem solve Get help with specific problems with your technologies, process and projects.

Importing data into different tablespace with a different user name

I need to export from user name 'A' from a production database and import it to a development database with a different user name 'B' and a different tablespace. My questions are:

  1. Do you have very detailed step-by-step procedure?
  2. Are there tricky parts or any special attention needed?

This is a pretty common procedure, which isn't too hard to accomplish. So here is a step-by-step guide.

  1. Export the entire schema from production:
       exp userid=system file=user_a.dmp log=user_a.log
  2. FTP the dump file (in BINARY mode) to the development server.
  3. Make sure user B exists in your development instance.
  4. imp userid=system file=user_a.dmp log=imp.log fromuser=a touser=b
The only big sticking point is that user B may have a different default tablespace then user A, if user A exists. If this is the case, and user B has been granted the CONNECT role, then user B's tables will be created in user A's default tablespace. To stop this, make sure that user B has the correct default tablespace and make sure that you revoke UNLIMITED TABLESPACE from user B.

For More Information

Dig Deeper on Oracle database design and architecture

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.