0

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

3

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
    
  • If my tablesize is large and I am not having a space to export that file then what should I do ? – Paresh Zawar Aug 28 '15 at 7:11
  • @Delta: It seems that you assume that the datafiles are included in the export file, That is wrong. You have to copy the data files. – miracle173 Aug 28 '15 at 11:46
  • @Paresh: The datafile is rather small because it contains only the metadata – miracle173 Aug 28 '15 at 11:47
  • @miracle173 But my tablespace size is in GB and I don't have that much space is current drive so what should I do (as I think EXPDUMP will required equal amount of memory as tablespace size)? – Paresh Zawar Aug 31 '15 at 2:57
  • @Paresh: No, that is wrong. The export file is small if you do as tranportable tablespace export. – miracle173 Aug 31 '15 at 5:52

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.