Transporting Tablespaces

The tablespaces being transported can be either dictionary managed or locally managed. The transported tablespaces are not required to be of the same block size as the target database standard block size.

Note: This method for transporting tablespaces requires that you place the tablespaces to be transported in read-only mode until you complete the transporting process. If this is undesirable, you can use the transportable tablespaces from backup feature

You must use Data Pump for transportable tablespaces. The only circumstance under which you can use the original import and export utilities, IMP and EXP, is for a backward migration of XMLType data to an Oracle Database 10g Release 2 (10.2) or earlier database.

（一）Limitations on Transportable Tablespaces

Be aware of the following limitations for transportable tablespaces:

1. The general limitations described in "About Transport Data" apply to transportable tablespaces.
2. When transporting a tablespace set, 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.
3. Transportable tablespaces cannot transport tables with TIMESTAMP WITH TIMEZONE (TSTZ) data across platforms with different time zone file versions. These tables are skipped automatically in a transportable tablespaces operation. These tables can be exported and imported conventionally.
4. Administrative tablespaces, such as SYSTEM and SYSAUX, cannot be included in a transportable tablespace set.

（二）Transporting Tablespaces Between Databases

These tasks for transporting a tablespace are illustrated more fully in the example that follows, where it is assumed the following data files and tablespaces exist:

Tablespace	Data File
sales_1	/u01/app/oracle/oradata/salesdb/sales_101.dbf
sales_2	/u01/app/oracle/oradata/salesdb/sales_201.dbf

Task 1: Pick a Self-Contained Set of Tablespaces

You can only transport a set of tablespaces that is self-contained. Some examples of self contained tablespace violations are:

1. An index inside the set of tablespaces is for a table outside of the set of tablespaces.

Note:It is not a violation if a corresponding index for a table is outside of the set of tablespaces.

1. A partitioned table is partially contained in the set of tablespaces.
2. A referential integrity constraint points to a table across a set boundary.

When transporting a set of tablespaces, you can choose to include referential integrity constraints. If you decide not to transport constraints, then the constraints are not considered as pointers.

1. A table inside the set of tablespaces contains a LOB column that points to LOBs outside the set of tablespaces.
2. An XML DB schema (*.xsd) that was registered by user A imports a global schema that was registered by user B, and the following is true: the default tablespace for user A is tablespace A, the default tablespace for user B is tablespace B, and only tablespace A is included in the set of tablespaces.

The following statement can be used to determine whether tablespaces sales_1 and sales_2 are self-contained, with referential integrity constraints taken into consideration (indicated by TRUE).

EXECUTE DBMS_TTS.TRANSPORT_SET_CHECK('sales_1,sales_2', TRUE);

After invoking this PL/SQL package, you can see all violations by selecting from the TRANSPORT_SET_VIOLATIONS view. If the set of tablespaces is self-contained, this view is empty.

Task 2: Generate a Transportable Tablespace Set

To generate a transportable tablespace set:

1. Start SQL*Plus and connect to the database as an administrator or as a user who has either the ALTER TABLESPACE or MANAGE TABLESPACE system privilege.
2. Make all tablespaces in the set read-only.

ALTER TABLESPACE sales_1 READ ONLY;

ALTER TABLESPACE sales_2 READ ONLY;

1. Invoke the Data Pump export utility as a user with DATAPUMP_EXP_FULL_DATABASE role and specify the tablespaces in the transportable set.

$ expdp user_name dumpfile=expdat.dmp directory=data_pump_dir

        transport_tablespaces=sales_1,sales_2 logfile=tts_export.log

Note: In a non-CDB, the directory object DATA_PUMP_DIR is created automatically. Read and write access to this directory is automatically granted to the DBA role, and thus to users SYS and SYSTEM.

However, the directory object DATA_PUMP_DIR is not created automatically in a PDB. Therefore, when importing into a PDB, create a directory object in the PDB and specify the directory object when you run Data Pump.

To perform a transport tablespace operation with a strict containment check, use the TRANSPORT_FULL_CHECK parameter, as shown in the following example:

expdp use_name dumpfile=expdat.dmp directory=data_pump_dir

      transport_tablespaces=sales_1,sales_2 transport_full_check=y

      logfile=tts_export.log

Task 3: Transport the Export Dump File

Transport the dump file to the directory pointed to by the DATA_PUMP_DIR directory object, or to any other directory of your choosing. The new location must be accessible to the target database.

At the target database, run the following query to determine the location of DATA_PUMP_DIR:

SELECT * FROM DBA_DIRECTORIES WHERE DIRECTORY_NAME = 'DATA_PUMP_DIR';

Task 4: Transport the Tablespace Set

In this example, transfer the following files from the source database to the target database:

sales_101.dbf

sales_201.dbf

注意在传输文件前查看平台endian是否一致，如果不同需要使用DBMS_FILE_TRANSFER的GET_FILE或PUT_FILE存储过程传输或传输前使用RMAN CONVERT转换

SELECT d.PLATFORM_NAME, ENDIAN_FORMAT

     FROM V$TRANSPORTABLE_PLATFORM tp, V$DATABASE d

     WHERE tp.PLATFORM_NAME = d.PLATFORM_NAME;

Task 5: (Optional) Restore Tablespaces to Read/Write Mode

Make the transported tablespaces read/write again at the source database.

ALTER TABLESPACE sales_1 READ WRITE;

ALTER TABLESPACE sales_2 READ WRITE;

Task 6: Import the Tablespace Set

To complete the transportable tablespaces operation, import the tablespace set.

To import the tablespace set:

impdp user_name dumpfile=expdat.dmp directory=data_pump_dir

   transport_datafiles=

   'c:\app\orauser\oradata\orawin\sales_101.dbf',

   'c:\app\orauser\oradata\orawin\sales_201.dbf'

   remap_schema=sales1:crm1  remap_schema=sales2:crm2

   logfile=tts_import.log

After this statement executes successfully, all tablespaces in the set being copied remain in read-only mode. If required, put the tablespaces into read/write mode on the target database.