How to copy tablespace from one database to another database in Oracle 11g

Question

How to copy tablespace and datafiles from one database to another database in Oracle 11g in same server ?

Answer 1

While copying a tablespace, it automatically includes data files and indexes associated with it. You need to make sure that tablespace is self contained.

1. Confirm that tablespace is self contained
   execute DBMS_TTS.TRANSPORT_SET_CHECK('<TABLESPACENAME>', true);

2. Use the TRANSPORT_SET_VIOLATIONS view to check if any violations condition exists
   select * from TRANSPORT_SET_VIOLATIONS;
   if any exist then fix them.

3. Make the tablespace read only
   alter tablespace <TABLESPACENAME> read only;

4. Use EXPDP COMMAND to export the tablespace:

   EXPDP USERNAME/PASSWORD DIRECTORY=DIRECTORYNAME DUMPFILE=DUMPFILE.DMP
   LOGFILE=ERROR.LOG TRANSPORT_TABLESPACE=TABLESPACENAME
   

   (You should have the rights necessary to execute IMPDP/EXPDP command and access rights to directory objects which map to the OS folder.)

5. Copy the datafiles to the target database using ftp or scp or somt other filesystem utility.

6. Make the tablespace READ WRITE again in the source database

7. Use IMPDP command to import it into target database:

   IMPDP USERNAME/PASSWORD DIRECTORY=DIRECTORYNAME DUMPFILE=DUMPFILENAME.DMP 
   LOGFILE=ERROR.LOG
   

8. Check logfile for errors, if any

9. Make the tablespace read write in the target database:

   ALTER TABLESPACE <TABLESPACENAME> READ WRITE