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

backupdatafileoracle-11g-r2restoretablespaces

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

Best Answer

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
Related Question