110
DBMS_TTS

The DBMS_TTS package checks if the transportable set is self-contained. All violations are inserted into a temporary table that can be selected from the view TRANSPORT_SET_VIOLATIONS.

This chapter contains the following topics:

• Using DBMS_TTS
  • Security Model
  • Exceptions
  • Operational Notes
• Summary of DBMS_TTS Subprograms

---

Using DBMS_TTS

• Security Model
• Exceptions
• Operational Notes

---

Security Model

Only users having the execute_catalog_role can execute this procedure. This role is initially only assigned to user SYS.

---

Exceptions

ts_not_found  EXCEPTION;
PRAGMA exception_init(ts_not_found, -29304);
ts_not_found_num NUMBER := -29304;
 
invalid_ts_list  EXCEPTION;
PRAGMA exception_init(invalid_ts_list, -29346);
invalid_ts_list_num NUMBER := -29346;

sys_or_tmp_ts                   EXCEPTION; 
PRAGMA exception_init(sys_or_tmp_ts, -29351); 
sys_or_tmp_ts_num NUMBER := -29351;

---

Operational Notes

With respect to transportable tablespaces, disabled and enabled referential integrity constraints are handled differently:

• A disabled referential integrity constraint does not violate the transportability rules and is dropped during the import phase.
• An enabled referential integrity constraint violates the transportability rules if it references a table in a tablespace outside the transportable set.

---

Summary of DBMS_TTS Subprograms

These two procedures are designed to be called by database administrators.

Table 110-1  DBMS_TTS Package Subprograms

Subprogram	Description
DOWNGRADE Procedure	Downgrades transportable tablespace related data
TRANSPORT_SET_CHECK Procedure	Checks if a set of tablespaces (to be transported) is self-contained

---

DOWNGRADE Procedure

This procedure downgrades transportable tablespace related data.

Syntax

DBMS_TTS.DOWNGRADE;

---

TRANSPORT_SET_CHECK Procedure

This procedure checks if a set of tablespaces (to be transported) is self-contained. After calling this procedure, the user may select from a view to see a list of violations, if there are any.

Syntax

DBMS_TTS.TRANSPORT_SET_CHECK (
   ts_list          IN VARCHAR2, 
   incl_constraints IN BOOLEAN DEFAULT FALSE,
   full_check       IN BOOLEAN DEFAULT FALSE);

Parameters

Table 110-2 TRANSPORT_SET_CHECK Procedure Parameters

Parameter	Description
ts_list	List of tablespace, separated by comma.
incl_constraints	TRUE if you want to count in referential integrity constraints when examining if the set of tablespaces is self-contained. (The incl_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.

Examples

If the view does not return any rows, then the set of tablespaces is self-contained. For example,

SQLPLUS> EXECUTE DBMS_TTS.TRANSPORT_SET_CHECK('foo,bar', TRUE);
SQLPLUS> SELECT * FROM TRANSPORT_SET_VIOLATIONS;