Task 1: Pick a Self-Contained Set of Tablespaces

See how to determine if the tablespaces you want to use are self-contained, or have dependencies.

There may be logical or physical dependencies between the database objects in the transportable set, and the database objects outside of the transportable set. You can only transport a tablespace set that is self-contained, that is, none of the database objects inside a tablespace set are dependent on any of the database objects outside of that tablespace set.

Some examples of self-contained tablespace violations are:

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

  • A partitioned table is partially contained in the set of tablespaces.

    The tablespace set that you want to copy must contain either all partitions of a partitioned table, or none of the partitions of a partitioned table. To transport a subset of a partition table, you must exchange the partitions into tables.

    See Oracle Database VLDB and Partitioning Guide for information about exchanging partitions.

  • 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. However, doing so can affect whether a set of tablespaces is self-contained. If you decide not to transport constraints, then the constraints are not considered as pointers.

  • A table inside the set of tablespaces contains a LOB column that points to LOBs outside the set of tablespaces.

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

To determine whether a set of tablespaces is self-contained, run the TRANSPORT_SET_CHECK procedure in the Oracle-supplied package DBMS_TTS. To run this procedure, you must have been granted the EXECUTE_CATALOG_ROLE role (initially signed to SYS).

When you run the DBMS_TTS.TRANSPORT_SET_CHECK procedure, specify the list of tablespaces in the transportable set to be checked for self containment. You can optionally specify if constraints must be included. For strict or full containment, you must additionally set the TTS_FULL_CHECK parameter to TRUE.

The strict or full containment check is for cases that require capturing not only references going outside the transportable set, but also those coming into the set. Tablespace Point-in-Time Recovery (TSPITR) is one such case where dependent objects must be fully contained or fully outside the transportable set.

For example, it is a violation to perform TSPITR on a tablespace containing a table t, but not its index i, because the index and data will be inconsistent after the transport. A full containment check ensures that there are no dependencies going outside or coming into the transportable set. See the example for TSPITR in the Oracle Database Backup and Recovery User’s Guide.

Note:

The default for transportable tablespaces is to check for self-containment, rather than full containment.

Example 5-2 Determining Whether Tablespaces are Self-Contained

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 running the DBMS_TTS.TRANSPORT_SET_CHECK procedure, you can see all the violations by selecting from the TRANSPORT_SET_VIOLATIONS view. If the set of tablespaces is self-contained, then this view is empty. The following example illustrates a case where there are two violations: a foreign key constraint, dept_fk, across the tablespace set boundary, and a partitioned table, jim.sales, that is partially contained in the tablespace set. 

SELECT * FROM TRANSPORT_SET_VIOLATIONS;

VIOLATIONS
---------------------------------------------------------------------------
Constraint DEPT_FK between table JIM.EMP in tablespace SALES_1 and table
JIM.DEPT in tablespace OTHER
Partitioned table JIM.SALES is partially contained in the transportable set

You must resolve these violations before sales_1 and sales_2 are transportable. One choice for bypassing the integrity constraint violation is to not to export the integrity constraints.

Related Topics

Parent topic: Migrating Oracle Database