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

Copying tablespaces to another server

I am trying to duplicate the tablespace in another server. I copied the tablespace (.dbf) file from server A to B where the other tablespaces resided in B. But the tables are not present. Please help.

I am trying to duplicate the tablespace in another server. I copied the tablespace (.dbf) file from server A to B where the other tablespaces resided in B.

I used the CREATE TABLESPACE with REUSE option. Then I created a user and made default to the newly created tablespace in B. But the tables are not present.

I also tried the export /import option. I tried to export the user schema and copied the .dmp file to server B and imported to the same user. But that did not work.

When I tried the export table option, it worked. I want all the objects of the tablespace in server A to be moved to B with the data. Please help.

You cannot simply copy a datafile to another database and expect it to work. However, you can use Oracle's Transportable Tablespaces to copy the data. The steps are as follows:

  1. On the source database, use the DBMS_TTS package to ensure that the tablespace(s) is a self-contained set.
  2. Place the tablespace in READ ONLY mode.
  3. Use the exp utility to generate a metadata dump file.
  4. Copy the tablespace's datafiles and the metadata dump file to the destination server.
  5. Use the imp utility on the destination server to plug in the tablespace.

I suggest reading the Oracle Utilities guide for further information on Transportable Tablespaces.

Dig Deeper on Oracle database backup and recovery

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.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchHRSoftware

Close