傳輸表空間自包含理解

【摘於】

對於可傳輸表空間有一個重要概念：自包含（Self-Contained）。
在表空間傳輸的中，要求表空間集為自包含的，自包含表示用於傳輸的內部表空間集沒有引用指向外部表空間集。自包含分為兩種：一般自包含表空間集和完全（嚴格）自包含表空間集。
常見的以下情況是違反自包含原則的：
 索引在內部表空間集，而表在外部表空間集（相反地，如果表在內部表空間集，而索引在外部表空間集，則不違反自包含原則）。
 分割槽表一部分割槽在內部表空間集，一部分在外部表空間集（對於分割槽表，要麼全部包含在內部表空間集中，要麼全不包含）。
 如果在傳輸表空間時同時傳輸約束，則對於引用完整性約束，約束指向的表在外部表空間集，則違反自包含約束；如果不傳輸約束，則與約束指向無關。
 表在內部表空間集，而lob列在外部表空間集，則違反自包含約束。
通常可以透過系統包DBMS_TTS來檢查表空間是否自包含，驗證可以以兩種方式執行：非嚴格方式和嚴格方式。
以下是一個簡單的驗證過程，假定在eygle表空間存在一個表eygle，其上存在索引儲存在USERS表空間：

SQL> create table eygle as select rownum id ,username from dba_users;
Table created.

SQL> create index ind_id on eygle(id) tablespace users;
Index created.

以SYS使用者執行非嚴格自包含檢查（full_check=false）：

SQL> connect / as sysdba
Connected.
SQL> exec dbms_tts.transport_set_check('EYGLE', TRUE);
PL/SQL procedure successfully completed.

SQL> SELECT * FROM TRANSPORT_SET_VIOLATIONS;
no rows selected

執行嚴格自包含檢查（full_check=true）：

SQL> exec dbms_tts.transport_set_check('EYGLE', TRUE, True);
PL/SQL procedure successfully completed.

SQL> SELECT * FROM TRANSPORT_SET_VIOLATIONS;
VIOLATIONS
--------------------------------------------------------------------------------------
Index EYGLE.IND_ID in tablespace USERS points to table EYGLE.EYGLE in tablespace EYGLE

反過來對於USERS表空間來說，非嚴格檢查也是無法透過的：

SQL> exec dbms_tts.transport_set_check('USERS', TRUE);
PL/SQL procedure successfully completed.

SQL> SELECT * FROM TRANSPORT_SET_VIOLATIONS;
VIOLATIONS
----------------------------------------------------------------------------------------
Index EYGLE.IND_ID in tablespace USERS points to table EYGLE.EYGLE in tablespace EYGLE

但是可以對多個表空間同時傳輸，則一些自包含問題就可以得到解決：

SQL> exec dbms_tts.transport_set_check('USERS,EYGLE', TRUE, True);
PL/SQL procedure successfully completed.

SQL> SELECT * FROM TRANSPORT_SET_VIOLATIONS;
no rows selected

官方解釋如下：

There may be logical or physical dependencies between objects in the transportable set and those outside of the set. You can only transport a set of tablespaces that is self-contained. In this context "self-contained" means that there are no references from inside the set of tablespaces pointing outside of the tablespaces. 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 you want to copy must contain either all partitions of a partitioned table, or none of the partitions of a partitioned table. If you want to transport a subset of a partition table, you must exchange the partitions into tables.

• 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 or not 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, you can invoke the TRANSPORT_SET_CHECK procedure in the Oracle supplied package DBMS_TTS. You must have been granted the EXECUTE_CATALOG_ROLE role (initially signed to SYS) to execute this procedure.

When you invoke the DBMS_TTS package, you 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.

incl_constraints

TRUE if you want to count in referential integrity constraints when examining if the set of tablespaces is self-contained. (Theincl_constraints parameter is a default so that TRANSPORT_SET_CHECK will work if it is called with only the ts_list argument.)

full_check

Indicates whether a full or partial dependency check is required. If TRUE, treats all IN and OUT pointers (dependencies) and captures them as violations if they are not self-contained in the transportable set. The parameter should be set to TRUE for TSPITR or if a strict version of transportable is desired. By default the parameter is set to false. It will only consider OUT pointers as violations.