How to copy tablespace and datafiles from one database to another database in Oracle 11g in same server ?
1 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.
Confirm that tablespace is self contained
execute DBMS_TTS.TRANSPORT_SET_CHECK('<TABLESPACENAME>', true);
Use the TRANSPORT_SET_VIOLATIONS view to check if any violations condition exists
select * from TRANSPORT_SET_VIOLATIONS;
if any exist then fix them.Make the tablespace read only
alter tablespace <TABLESPACENAME> read only;
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.)Copy the datafiles to the target database using
ftp
orscp
or somt other filesystem utility.Make the tablespace
READ WRITE
again in the source databaseUse
IMPDP
command to import it into target database:IMPDP USERNAME/PASSWORD DIRECTORY=DIRECTORYNAME DUMPFILE=DUMPFILENAME.DMP LOGFILE=ERROR.LOG
Check logfile for errors, if any
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 ?– PareshAug 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. Aug 28, 2015 at 11:46
-
@Paresh: The datafile is rather small because it contains only the metadata 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)?– PareshAug 31, 2015 at 2:57
-
@Paresh: No, that is wrong. The export file is small if you do as tranportable tablespace export. Aug 31, 2015 at 5:52