0

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

0

1 Answer 1

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
    
6
  • If my tablesize is large and I am not having a space to export that file then what should I do ?
    – Paresh
    Aug 28, 2015 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, 2015 at 11:46
  • @Paresh: The datafile is rather small because it contains only the metadata
    – miracle173
    Aug 28, 2015 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
    Aug 31, 2015 at 2:57
  • @Paresh: No, that is wrong. The export file is small if you do as tranportable tablespace export.
    – miracle173
    Aug 31, 2015 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.