(一)TRANSPORTABLE = [ALWAYS | NEVER默认]
The TRANSPORTABLE=ALWAYS parameter is supplied on a table mode export (specified with the TABLES parameter) or a full mode export (specified with the FULL parameter) or a full mode network import (specified with the FULL and NETWORK_LINK parameters).
实质上它们还是使用传输表空间:
In a table mode export, using the transportable option results in a transportable tablespace export in which metadata for only the specified tables, partitions, or subpartitions is exported.
In a full mode export, using the transportable option results in a full transportable export which exports all objects and data necessary to create a complete copy of the database.
限制:
Restrictions
- The TRANSPORTABLE parameter is only valid in table mode exports and full mode exports.
- To use the TRANSPORTABLE parameter, the COMPATIBLE initialization parameter must be set to at least 11.0.0.
- The user performing a transportable export requires the DATAPUMP_EXP_FULL_DATABASE privilege.
- Tablespaces associated with tables, partitions, and subpartitions must be read-only.
- Full mode export
$ expdp user_name FULL=y DUMPFILE=expdat.dmp DIRECTORY=data_pump_dir TRANSPORTABLE=always VERSION=12.0 LOGFILE=export.log
同时使用TRANSPORTABLE=ALWAYS以及FULL选项,则使用全库的传输导出. 会把库的表空间分两类:可传输表空间与不可传输表空间(如SYSTEM,SYSAUX表空间),会导出包含在传输表空间对象的元信息,以及对不可传输表空间上对象使用direct path以及external table方式导出元信息与数据
Objects residing in non-transportable tablespaces (for example, SYSTEM and SYSAUX) have both their metadata and data unloaded into the dump file set, using direct path unload and external tables.
Performing a full transportable export has the following restrictions:
1)The default tablespace of the user performing the export must not be set to one of the tablespaces being transported.
2) If the database being exported contains either encrypted tablespaces or tables with encrypted columns (either Transparent Data Encryption (TDE) columns or SecureFiles LOB columns), then the ENCRYPTION_PASSWORD parameter must also be supplied.
3) The source and target databases must be on platforms with the same endianness if there are encrypted tablespaces in the source database.
4) If the source platform and the target platform are of different endianness, then you must convert the data being transported so that it is in the format of the target platform. You can use the DBMS_FILE_TRANSFER package or the RMAN CONVERT command to convert the data.
5) A full transportable export is not restartable.
6) All objects with storage that are selected for export must have all of their storage segments either entirely within administrative, non-transportable tablespaces (SYSTEM / SYSAUX) or entirely within user-defined, transportable tablespaces. Storage for a single object cannot straddle the two kinds of tablespaces. 即类似自包的意思,导出对象及其关联对象要么都在不传输的表空间中,要么在传输表空间中
7) When transporting a database over the network using full transportable export, auditing cannot be enabled for tables stored in an administrative tablespace (such as SYSTEM and SYSAUX) if the audit trail information itself is stored in a user-defined tablespace.
8) To use the FULL parameter in conjunction with TRANSPORTABLE (a full transportable export), either the Data Pump VERSION parameter must be set to at least 12.0. or the COMPATIBLE database initialization parameter must be set to at least 12.0 or later.
9) Full transportable exports are supported from a source database running release 11.2.0.3. To do so, set the Data Pump VERSION parameter to at least 12.0
如果是基于网络的全库传输导入(即impdp使用了network_link)则导入时需要同时指定full=y, transportable=always以及trnasport_datafiles=datafile_name选项
如果是基于dumpfile的全库传输导入,只需要指定trnasport_datafiles=datafile_name参数,它会自动判断dumpfile是否使用full=y, transportable=always导出的.
如果使用network_link选项要求远程源库必须要11.2.0.3及之后版本,且version最低设置为12.如果是基于文件导入则不需要指定version
If you are using a network link, then the database specified on the NETWORK_LINK parameter must be Oracle Database 11g release 2 (11.2.0.3) or later, and the Data Pump VERSION parameter must be set to at least 12. (In a non-network import, VERSION=12 is implicitly determined from the dump file.)
- Table mode export
Metadata for the specified tables, partitions, or subpartitions is exported to the dump file. To move the actual data, you copy the data files to the target database.
关于导出分区表:
If only a subset of a table's partitions are exported and the TRANSPORTABLE=ALWAYS parameter is used, then on import each partition becomes a non-partitioned table.
If only a subset of a table's partitions are exported and the TRANSPORTABLE parameter is not used at all or is set to NEVER (the default), then on import:
- If PARTITION_OPTIONS=DEPARTITION is used, then each partition included in the dump file set is created as a non-partitioned table.
- If PARTITION_OPTIONS is not used, then the complete table is created. That is, all the metadata for the complete table is present so that the table definition looks the same on the target system as it did on the source. But only the data that was exported for the specified partitions is inserted into the table.
(二)传输表空间
- TRANSPORT_TABLESPACES=tablespace_name [, ...]
$ expdp hr DIRECTORY=dpump_dir1 DUMPFILE=tts.dmp TRANSPORT_TABLESPACES=tbs_1 TRANSPORT_FULL_CHECK=YES LOGFILE=tts.log
日志文件会列出需要cp的数据文件
The log file for the export lists the data files that are used in the transportable set, the dump files, and any containment violations.
限制:
1)不能使用传输表空间方式导入到低于源库版本的数据库
You cannot export transportable tablespaces and then import them into a database at a lower release level. The target database must be at the same or later release level as the source database.
Restrictions
- Transportable tablespace jobs are not restartable.
- Transportable tablespace jobs are restricted to a degree of parallelism of 1.
- Transportable tablespace mode requires that you have the DATAPUMP_EXP_FULL_DATABASE role.
- The default tablespace of the user performing the export must not be set to one of the tablespaces being transported.
- The SYSTEM and SYSAUX tablespaces are not transportable in transportable tablespace mode.
- All tablespaces in the transportable set must be set to read-only.
- If the Data Pump Export VERSION parameter is specified along with the TRANSPORT_TABLESPACES parameter, then the version must be equal to or greater than the Oracle Database COMPATIBLE initialization parameter.
- TRANSPORT_FULL_CHECK=[YES | NO]
设置为YES相当于使用DBMS_TTS的full_check, 为NO则只是单向检查表空间是否自包
If TRANSPORT_FULL_CHECK=YES, then Export verifies that there are no dependencies between those objects inside the transportable set and those outside the transportable set.
The check addresses two-way dependencies:
For example, if a table is inside the transportable set but its index is not, then a failure is returned and the export operation is terminated. Similarly, a failure is also returned if an index is in the transportable set but the table is not.
If TRANSPORT_FULL_CHECK=NO, then Export verifies only that there are no objects within the transportable set that are dependent on objects outside the transportable set. This check addresses a one-way dependency.
For example, a table is not dependent on an index, but an index isdependent on a table, because an index without a table has no meaning. Therefore, if the transportable set contains a table, but not its index, then this check succeeds. However, if the transportable set contains an index, but not the table, then the export operation is terminated.
There are other checks performed as well. For instance, export always verifies that all storage segments of all tables (and their indexes) defined within the tablespace set specified by TRANSPORT_TABLESPACES are actually contained within the tablespace set.
(三)传输导入
IMPDP包含与EXPDP类似参数:TRANSPORT_FULL_CHECK, TRANSPORT_TABLESPACES, TRANSPORTABLE, 它们必须配置NETWORK_LINK选项使用
TRANSPORT_DATAFILES=datafile_name
指定传输的数据文件,用于使用传输表空间导入,亦或是使用了table/full+TRANSPORTABLE=ALWAYS的导出方式导出文件。选项指定的数据文件的绝对路径(而不是使用directory选项),使用引号引志,数据文件名可以使用通配符(*与?),但路径不能使用通配。使用通配必须所有文件都用于传输数据集,如果找到有文件不是则会报错且导入中止。
Transportable import jobs cannot be restarted.
Example:
源库中tbs_1对应的数据文件为employees.dat
把数据文件复制到目标库为workers.dat
expdp hr DIRECTORY=dpump_dir1 DUMPFILE=tts.dmp TRANSPORT_TABLESPACES=tbs_1
impdp hr DIRECTORY=dpump_dir1 DUMPFILE=tts.dmp TRANSPORT_DATAFILES='/user01/data/workers.dat'