oracle tts exp,Oracle 传输表空间(TTS) 的限制与局限性

在使用Oracle 传输表空间时需要注意以下内容，否则无法正常使用。

Default DATA_PUMP_DIR does not work with PDBs

Restriction:  In a CDB, the default Data Pump directory object, DATA_PUMP_DIR, does not work with PDBs. You must define an explicit directory object within the PDB that you are using with Data Pump export/import.

Affected Version: 12c+

When using the IMPDP utility, and the option TRANSPORT_TABLESPACES

Restriction   :When using the IMPDP utility, and the option TRANSPORT_TABLESPACES.

- You cannot export transportable tablespaces and then import them into a database at a lower release level.  The target database into which you are importing must be   at the same or higher release level as the source database.

- The TRANSPORT_TABLESPACES parameter is valid only when the NETWORK_LINK parameter is also specified.

- Transportable mode does not support encrypted columns.

Reference   :  Oracle Database Utilities - Chapter 3 Data Pump Import

Affected Version :  All Versions

When using the IMPDP utility, the TRANSPORT_DATAFILES parameter

Restriction  :  When using the IMPDP utility, the TRANSPORT_DATAFILES parameter cannot be used in conjunction with the QUERY parameter.

Reference   :  Oracle Database Utilities - Chapter 3 Data Pump Import

Affected Version :  All Versions

When using the IMPDP utility, and the option TRANSPORT_FULL_CHECK

Restriction  :  When using the IMPDP utility, and the option TRANSPORT_FULL_CHECK.  This parameter is valid for transportable mode (or table mode when TRANSPORTABLE=ALWAYS was specified on the export) only when the NETWORK_LINK parameter is specified.

Reference   : Oracle Database Utilities - Chapter 3 Data Pump Import

Affected Version :  All Versions

When using the IMPDP utility, and the option TRANSPORTABLE

Restriction  :  When using the IMPDP utility, and the option TRANSPORTABLE.

- The Import TRANSPORTABLE parameter is valid only if the NETWORK_LINK parameter is also specified.

- The TRANSPORTABLE parameter is only valid in table mode imports (the tables do not have to be partitioned or subpartitioned).

- The user performing a transportable import requires the DATAPUMP_EXP_FULL_DATABASE role on the source database and the DATAPUMP_IMP_FULL_DATABASE role on the target database.

- To make full use of the TRANSPORTABLE parameter, the COMPATIBLE initialization parameter must be set to at least 11.0.0.

Reference   :  Oracle Database Utilities - Capter 3 Data Pump Import

Affected Version : Greater than 11.0.0https://www.cndba.cn/dave/article/4390

When using EXPDP utility, and the option TRANSPORT_TABLESPACES

Restriction :When using The EXPDP utility, and the option TRANSPORT_TABLESPACES

- Transportable jobs are not restartable.

- Transportable jobs are restricted to a degree of parallelism of 1.

- Transportable tablespace mode requires that you have the DATAPUMP_EXP_FULL_DATABASE role.

- Transportable mode does not support encrypted columns.

- 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.

- 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.

- The TRANSPORT_TABLESPACES parameter cannot be used in conjunction with the QUERY parameter.

- In RDBMS versions < 10.2.0.4 there was a 4K character-limit on the transportable_tablespaces parameter; a fix in 10.2.0.4 increased this to 32K.   The compatibility parameter must be at least 10.2.0.4 or higher to implement this higher limit.  (See Document 1131484.1 ExpdpTransportable Tablespace Fails With ORA-39071 for further details.)

Reference   :  Oracle Database Utilities - Chapter 3 Data Pump Import

Affected Version : Greater than 11.0.0, except where noted.

Index Organized Tables (IOT) can become corrupt when using Transportable Tablespace

Restriction   : Index Organized Tables (IOT) can become corrupt when using Transportable Tablespace (TTS) from Solaris, Linux or AIX  to HP/UX.   Currently there is no patch for this issue, the Index Organized Tables (IOT) need to be recreated after the TTS.

References  : Document 371556.1 How to Move Tablespaces Across Platforms Using Transportable Tablespaces With RMAN, Bug 9816640 closed as not feasible to fix.

Affected Version :All Versions currently (check enhancement Bug 12683199 for version implemented).

https://www.cndba.cn/dave/article/4390

It is not possible to transport the SYSTEM, SYSAUX, TEMP tablespaces or objects owned by the user SYS

Restriction   : SYSTEM, SYSAUX, TEMP Tablespace Objects.  It is not possible to transport the SYSTEM, SYSAUX, and TEMP tablespaces or objects owned by the user SYS. Some examples of such objects are PL/SQL, Java classes, callouts, views, triggers,  dblinks, roles, procedures, synonyms, users, privileges, dimensions, directories, sequences, and temporary tables.   These must be created separately from the TTS operation,  with scripts or export/import.

Note:  The following Database Schemas/Users are also treated as SYS:  'CTXSYS, 'ORDSYS', 'MDSYS', 'ORDPLUGINS', 'LBACSYS', 'XDB', 'SI_INFORMTN_SCHEMA', 'DIP', 'DMSYS', 'DBSNMP', 'ORDDATA' are treated as objects owned by 'SYS' and not exported with TTS.

Reference    : Oracle Database Administrator's Guide - Transporting Tablespaces Between Databases.

Affected Version : All versionshttps://www.cndba.cn/dave/article/4390

Transportable Tablespace import using IMPDP fails when the tablespace contains a spatial index

Restriction   :  Transportable Tablespace import using IMPDP fails when the tablespace contains a spatial index defined in it.

Reference    :  Document 579136.1 IMPDP TRANSPORTABLE TABLESPACE FAILS for SPATIAL INDEX

Document 2031174.1 TRANSPORTABLE TABLESPACE IMPORT FAILS WITH ORA-39083 ORA-942 ON MDRS TABLES

Jobs performed in transportable tablespace mode have specific requirements to prevent the EXPDP/IMPDP job from failing

Restriction   :  Jobs performed in transportable tablespace mode have specific requirements to prevent the IMPDP job from failing, before anything is imported.  Starting in 12.1+, it will not cause a job failure, but the tables with the TSLTZ data are not transported if there is a source/target mismatch of the timezone files.

References  :Oracle Database Utilities  -  Chapter 2 Data Pump Export / Chapter 3 Data Pump Import,

Oracle Database Administrator's Guide -  Considerations for Time Zone File Versions in Transportable Tablespace Mode,

Document 1275433.1 Data Pump TTS Import Fails With ORA-39002 And ORA-39322 Due To TIMEZONE Conflict.

Affected Version :  All Versions (Documented since 11.2 per doc Bug 10417805 fixed.

Exporting a Schema In an 11.2 Database With a 10g Client Gives ORA-1455

Restriction  :  Exporting a Schema In an 11.2 Database With a 10g IMP/EXP Client Gives ORA-1455.  This is because on 11.2, by default deferred segment creation feature is turned on. When a table is created with locally managed tablespace then its property value is very big. If we insert any row in the table then it's segment gets created and property value updated to 536870912.

Reference   :  This limitation is discussed in Document 1166564.1 Master Note for Transportable Tablespaces (TTS) -- Common Questions and Issues

Affected Version :  All versions

Considerations for Time Zone File Versions in Transportable Tablespace Mode

Restriction :  Jobs performed in transportable tablespace mode have the following requirements concerning time zone file versions:  If the source database is Oracle Database 12c Release 1 (12.1.0.1) or later and the timezone files mismatch between source and target, data pump will not fail, but tables with the TSLTZ data are not transported.  If the source is Oracle Database 11g release 2 (11.2.0.2) or later but less than 12.1.0.1, and there are tables in the transportable set that use TIMESTAMP WITH TIMEZONE (TSTZ) columns, then the time zone file version on the target database must exactly match the time zone file version on the source database.

If the source is earlier than Oracle Database 11g release 2 (11.2.0.2), then the time zone file version must be the same on the source and target database

for all transportable jobs regardless of whether the transportable set uses TSTZ columns.

If these requirements are not met, then the import job aborts before anything is imported. This is because if the import job were allowed to import the

objects, there might be inconsistent results when tables with TSTZ columns were read.

To identify the time zone file version of a database, you can execute the following SQL statement:

SQL> SELECT VERSION FROM V$TIMEZONE_FILE;

And

This select gives all TimeStamp with Time Zone (TSTZ) columns in your database:

select c.owner || '.' || c.table_name || '(' || c.column_name || ') -'

|| c.data_type || ' ' col

from dba_tab_cols c, dba_objects o

where c.data_type like '%WITH TIME ZONE'

and c.owner=o.owner

and c.table_name = o.object_name

and o.object_type = 'TABLE'

order by col

/

https://www.cndba.cn/dave/article/4390

Reference  :   For IMPDP considerations click on the following link: 11gR2 Database utilities guide.

For EXPDP considerations click on the following link: 11gR2 Database utilities guide.

For Limitations on Transportable Tablespaces click on the following link: 12cR1 Database Administrator's Guide

Affected Version :   11g onwards

Character Set and National Character Set Limitation

Restriction :  The source and target database must use a compatible character set and national character set.

https://www.cndba.cn/dave/article/4390

Tablespace with the same name already exists

Restriction :  You cannot transport a tablespace to a target database in which a tablespace with the same name already exists. However, you can rename either the

tablespace to be transported or the destination tablespace before the transport operation.

12g+: In a Container Data Base (CDB), you cannot transport a tablespace to a target container that contains a tablespace of the same name. However, different containers can have tablespaces with the same name.   As in 11g, you can use the REMAP_TABLESPACE import parameter to import the database objects into a different tablespace. Alternatively, before the transport operation, you can rename either the tablespace to be transported or the target tablespace.

Affected Version : All versions

Objects with Underlying Objects - Materialized Views or Partitioned Tables

Restriction :   Objects with underlying objects (such as materialized views) or contained objects (such as partitioned tables) are not transportable unless all of

the underlying or  contained objects are in the tablespace set.

- Review Table "Objects Exported and Imported in Each Mode" from the Oracle Database Utilities documentation, there are several object

types that are not exported in tablespace mode.

Objects where owner on destination is new

Restriction :  If the owner/s of tablespace objects does not exist on target database,  the usernames need to be created manually before starting the transportable

tablespace import.

Size Limitations

Restriction   : In expdp, when the number of tablespaces defined was too big, a set of errors was reported (ORA-39006: internal error, ORA-39065: unexpected master process exception in DISPATCH, ORA-06502: PL/SQL: numeric or value error: character string buffer too small, ORA-39097: Data Pump job encountered unexpected error -6502).

The buffer limitation for the query statement was 2000, changed to 32K in 11g.  A workaround is available in Document 566875.1 if patching is not possible.

References  : Document 566875.1 Size Limitations On Cross Platform Transportable Tablespaces, unpublished Bug 5249074 - EXPDP ERRORS WHEN MANY TRANSPORTABLE TABLESPACES DEFINED

Affected Version :All Versions lower than 11g; backported Patch 5249074 available for 10.2.0.3, 10.2.0.4.

Tablespace containing spatial indexes

Restriction :  Be aware that TTS across different endian platforms are not supported for spatial indexes in 10gR1 and 10gR2; such a limitation has been released in 11g

- specific Spatial packages must be run before exporting and after transportation, please see Oracle Spatial documentation.

Reference  :Oracle Spatial Developer's Guide

Affected Version :All versions

Tablespace containing XML Types

Restriction :   Beginning with Oracle Database 10g Release 2, you can transport tablespaces that contain XMLTypes, but you must use the IMP and EXP utilities, not Data Pump; when using EXP, ensure that the CONSTRAINTS and TRIGGERS parameters are set to Y (the default).  For versions 11.1 and higher, you must use only Data Pump, not the IMP and EXP Utilities.  This restriction on XML types and tables with binary XML storage continues in 12.2+.  For further details see the full documentation below:

The following query returns a list of tablespaces that contain XMLTypes:

https://www.cndba.cn/dave/article/4390

select distinct p.tablespace_name from dba_tablespaces p,

dba_xml_tables x, dba_users u, all_all_tables t where

t.table_name=x.table_name and t.tablespace_name=p.tablespace_name

and x.owner=u.username;

select  distinct p.tablespace_name

from  dba_tablespaces p,

dba_xml_tab_cols x,

dba_users u,

all_all_tables t

where t.table_name=x.table_name

and   t.tablespace_name=p.tablespace_name

and   x.owner=u.username;

Transporting tablespaces with XMLTypes has the following limitations:

-  The destination database must have XML DB installed.

-  Schemas referenced by XMLType tables cannot be the XML DB standard schemas.

-  Schemas referenced by XMLType tables cannot have cyclic dependencies.  (Limitation removed in 11.2.0.2 by unpublished Bug 4158608: ORA-03113 : REGISTER SCHEMA WITH CYCLIC DEFINITIONS.)

-  XMLType tables with row level security (RLS) are not supported, because they cannot be exported or imported.   Note that this limitation is not listed in the 12c documentation, but is an actual limitation for 12.1.  (Unpublished enhancement Bug 17319395 - TTS IMPORT DOES NOT IMPORT RLS POLICY FUNCTION for 12.1.0.2 was filed but still has request status.)

-  If the schema for a transported XMLType table is not present in the destination database, it is imported and registered. If the schema already exists in the destination database, an error is returned unless the ignore=y option is set.

-  If an XMLType table uses a schema that is dependent on another schema, the schema that is depended on is not exported. The import succeeds only if that schema is already in the destination database.  (Limitation removed in 11.1.0.7 by unpublished Bug 5950173 - EXPDP DOES NOT HANDLE XML SCHEMA DEPENDENCIES.)

- You must use only Data Pump to export and import the metadata for data that contains XMLTypes (11.1 and higher).

- Until 12.2, there is no support for binary storage XML.

- Beginning in 12.2.0.1 there is almost complete support for tablespace and fully transportable mode of TTS.  However, in 12.2.0.1, when exporting and importing a large dataset when an XMLIndex is present on the source table, we recommend either applying a patch for unpublished BUG 23333562 - "XDB:CDB:TTS IMPORT TABLESPACE LOW PERFORMANCE WITH 9-11HRS TO COMPLETE" (fixed in 12.2.0.2), or excluding XMLIndex at import using datapump parameter file.  For example, in exclude.par, add the following:

EXCLUDE=INDEX:"LIKE 'HR_XMLTAB_IDX%' "

and then use that parfile for the import.  This will avoid per-row index maintenance on the XML document during import when transformed to the destination encoding scheme.  After excluding the indexes and when import is complete, the index can be created and rebuilt as a whole.  This tip is per Development.

Using Advanced Queues in Transportable tablespaces

Restriction :  Advanced Queues Transportable tablespaces do not support 8.0-compatible advanced queues with multiple recipients.

Opaque Types (such as RAW, BFILE, and the AnyTypes) can be transported, however.

Restriction :  Opaque Types Types(such as RAW, BFILE, and the AnyTypes) can be transported, but they are not converted as part of the cross-platform transport operation.

Their actual structure is known only to the application, so the application must address any endianness issues after these types are moved to the new platform.

Reference : Oracle Database Administrators Guide - Transporting Tablespaces Between Databases - Limitations on Transportable Tablespace Use

Affected Version : All versions

Floating-Point Numbers BINARY_FLOAT and BINARY_DOUBLE types

Restriction :  Floating-Point Numbers BINARY_FLOAT and BINARY_DOUBLE types are transportable using Data Pump but not the original export utility, EXP.

https://www.cndba.cn/dave/article/4390

Traditional EXP/IMP with 11.2 is desupported

Restriction :   Before performing a TTS procedure ii it important to be aware that the use of traditional EXP/IMP with 11.2 is de-supported. Original Export is de-supported for general use as of Oracle Database 11g. The only supported use of original Export in Oracle Database 11g is backward migration of XMLType data to Oracle Database 10g release 2 (10.2) or earlier. Therefore, Oracle recommends that you use the new Data Pump Export and Import utilities, except in the following situations which require original Export and Import.

Traditional EXP/IMP Restrictions with Oracle 9i

Restriction :    When performing a TTS procedure with Oracle 9i it is important to be aware of the Objects Exported and Imported in the Tablespace Mode.  The document below details this.

Reference  :

Affected Version : 9i

Encrypted tablespaces have the following limitations

Restriction :  Before transporting an encrypted tablespace, you must copy the Oracle wallet manually to the destination database, unless the master encryption key is stored in a Hardware Security Module (HSM) device instead of an Oracle wallet. When copying the wallet, the wallet password remains the same in the destination database. However, it is recommended that you change the password on the destination database so that each database has its own wallet password. See Oracle Database Advanced Security Administrator's Guide for information about HSM devices, about determining the location of the Oracle wallet, and about changing the wallet password with Oracle Wallet Manager.

You cannot transport an encrypted tablespace to a database that already has an Oracle wallet for transparent data encryption. In this case, you must use Oracle Data Pump to export the tablespace's schema objects and then import them to the destination database. You can optionally take advantage of Oracle Data Pump features that enable you to maintain encryption for the data while it is being exported and imported. See Oracle Database Utilities for more information.

You cannot transport an encrypted tablespace to a platform with different endianness.

Tablespaces that do not use block encryption but that contain tables with encrypted columns cannot be transported. You must use Oracle Data Pump to export and import the tablespace's schema objects. You can take advantage of Oracle Data Pump features that enable you to maintain encryption for the data while it is being exported and imported. See Oracle Database Utilities for more information.

Affected Version : 11.2   (N.B.    TTS for TDE encrypted tablespaces or tablepaces containing tables with encrypted columns are no longer supported from 12c)

https://www.cndba.cn/dave/article/4390

Database Charactersets Compatibility between Source and Destination

Restriction :  The source and the destination databases must use compatible database character sets. That is, one of the following must be true:

- The database character sets of the source and the target databases are the same.

- The source database character set is a strict (binary) subset of the target database character set, and the following three conditions are true:

+  The source database is in version 10.1.0.3 or higher.

+  The tablespaces to be transported contain no table columns with character length semantics or the maximum character width is the same in both the source and target database character sets.

+  The tablespaces to be transported contain no columns with the CLOB data type, or the source and the target database character sets are both single-byte or both multibyte.

- The source database character set is a strict (binary) subset of the target database character set, and the following two conditions are true:

+  The source database is in a version lower than 10.1.0.3.

+  The maximum character width is the same in the source and target database character sets.

Compatibility Considerations for Transportable Tablespaces

When you create a transportable tablespace set, Oracle Database computes the lowest compatibility level at which the destination database must run. This is referred to as the compatibility level of the transportable set. Beginning with Oracle Database 11g, a tablespace can always be transported to a database with the same or higher compatibility setting, whether the destination database is on the same or a different platform. The database signals an error if the compatibility level of the transportable set is higher than the compatibility level of the destination database.

The following table shows the minimum compatibility requirements of the source and destination tablespace in various scenarios. The source and destination database need not have the same compatibility setting.

Table 14-1 Minimum Compatibility Requirementshttps://www.cndba.cn/dave/article/4390

Transport Scenario

Minimum Compatibility Setting

Source Database

Destination Database

Tablespace with different database block size than the destination database

9.0

9.0

Databases on different platforms

10.0

10.0

OLAP Application AW Transportability

Restriction :   OLAP Application AW Transportability

AW can only be transported via TTS if:

"AW transportability only works if the platform/operating system/word size/endianness/etc match."

If none or some of the above only match and AWs are included in in the TTS export, the TTS process will result in this error when importing:

ORA-37145: (XSTTS_PLAT) Cannot transport analytic workspace across platforms.

If this is the case then please reference the following MOS article:

How To Copy a 10.2 Olap Application From One System To Another (Doc ID 437595.1)

FROM:Transportable Tablespace (TTS) Restrictions and Limitations: Details, Reference, and Version Where Applicable (Doc ID 1454872.1)