Q
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
    owner=a
  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

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchHRSoftware

Close