Description:-
- In this article we are going to see the Oracle 18c Datapump- Transport_tablespaces
This parameter is used to perform an export in transportable-tablespace mode.It allows us to copy or move tablespaces between databases.
Syntax :
TRANSPORT_TABLESPACES=tablespace_name [, …]
Use the TRANSPORT_TABLESPACES parameter to specify a list of tablespace names for which object metadata will be exported from the source database into the target database.
- Keep USERSDEMO tablespace in Read only mode.
SQL> alter tablespace USERSDEMO read only;
Tablespace altered.
SQL> select name from v$datafile;
NAME
-----------------------------------------------------------
/u01/app/oracle/oradata/PROD18C/system01.dbf
/u01/app/oracle/oradata/PROD18C/dbwrtbs02.dbf
/u01/app/oracle/oradata/PROD18C/sysaux01.dbf
/u01/app/oracle/oradata/PROD18C/undotbs01.dbf
/u01/app/oracle/oradata/PROD18C/dbwrtbs01.dbf
/u01/app/oracle/oradata/PROD18C/users01.dbf
/u01/app/oracle/oradata/PROD18C/usersdemo.dbf
- Manually copy the datafile to the test18c server.
[oracle@18c empdata]$ scp /u01/app/oracle/oradata/PROD18C/usersdemo.dbf oracle@192.168.1.105:/u01/app/oracle/oradata/TEST/datafile
usersdemo.dbf 100% 2048MB 27.3MB/s 01:15
- Export the metadata of the tablespace USERSDEMO.
[oracle@18c empdata]$ expdp DUMPFILE=tts.dmp LOGFILE=tts_exp.log DIRECTORY=test_dir TRANSPORT_TABLESPACES=USERSDEMO Export: Release 18.0.0.0.0 - Production on Thu Aug 23 23:07:59 2018 Version 18.3.0.0.0 Copyright (c) 1982, 2018, Oracle and/or its affiliates. All rights reserved. Username: system Password: Connected to: Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production Starting "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01": system/******** DUMPFILE=tts.dmp LOGFILE=tts_exp.log DIRECTORY=test_dir TRANSPORT_TABLESPACES=USERSDEMO Processing object type TRANSPORTABLE_EXPORT/INDEX/STATISTICS/INDEX_STATISTICS Processing object type TRANSPORTABLE_EXPORT/STATISTICS/TABLE_STATISTICS Processing object type TRANSPORTABLE_EXPORT/STATISTICS/MARKER Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK Processing object type TRANSPORTABLE_EXPORT/TABLE Processing object type TRANSPORTABLE_EXPORT/INDEX/INDEX Processing object type TRANSPORTABLE_EXPORT/CONSTRAINT/CONSTRAINT Processing object type TRANSPORTABLE_EXPORT/COMMENT Master table "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" successfully loaded/unloaded ****************************************************************************** Dump file set for SYSTEM.SYS_EXPORT_TRANSPORTABLE_01 is: /u01/empdata/tts.dmp ****************************************************************************** Datafiles required for transportable tablespace USERSDEMO: /u01/app/oracle/oradata/PROD18C/usersdemo.dbf Job "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" successfully completed at Thu Aug 23 23:08:48 2018 elapsed 0 00:00:42
The metadata only (no data) for USERSDEMO tablespace is exported to a dump file named tts.dmp. The actual data was copied over to the target database using SCP.After copying the tts.dmp(metadata) and datafile usersdemo.dbf to target database.Import the dump using TRANSPORT_DATAFILES in target database.
Catch Me On:- Hariprasath Rajaram
Telegram:https://t.me/joinchat/I_f4DhGF_Zifr9YZvvMkRg
LinkedIn:https://www.linkedin.com/in/hari-prasath-aa65bb19/
Facebook:https://www.facebook.com/HariPrasathdba
FB Group:https://www.facebook.com/groups/894402327369506/
FB Page: https://www.facebook.com/dbahariprasath/?
Twitter: https://twitter.com/hariprasathdba
