Oracle DBA Lesson 88 Part 1 — Check the transport set

Transportable tablespaces let a database receive existing data files containing application data, while Data Pump supplies the object metadata. Before planning the file copy, decide whether the chosen tablespaces form a suitable set and whether the target can accept them.

Oracle DBA Lesson 88 Part 1 — Check the transport set

This lesson covers source dependency checks and source-to-target preflight. The write outage, metadata export, protected copy, import, and cutover sequence belong to the subsequent part. The baseline is Oracle Database 19c, with explicit source and target pluggable database services.

Why files alone are insufficient

A table's rows occupy blocks in physical data files. The files belong to tablespaces, but related objects can reside in different tablespaces. A table can have an index elsewhere, a foreign key that references a table elsewhere, or LOB storage elsewhere. A partitioned table can span several tablespaces.

Choose the transport set around the required object relationships. One application schema, one tablespace, and one physical file do not share one boundary. One tablespace can contain several data files, and the complete file list matters during the move.

For ordinary tablespace transport, administrative tablespaces such as SYSTEM and SYSAUX, plus undo and temporary tablespaces, stay out of the physical transport set.

See Transporting Data.

A candidate set and its dependencies

These checks use this arrangement:

Object	Tablespace	Relationship
APP_USER.ORDERS	APP_DATA	Application table
APP_USER.ORDERS_IX	APP_IDX	Index on ORDERS
APP_USER.CUSTOMERS	APP_REF	Table referenced by an enabled foreign key on ORDERS

Start with APP_DATA alone. The index is outside the candidate, and ORDERS_CUSTOMER_FK points from ORDERS to CUSTOMERS outside the candidate.

Two options of DBMS_TTS.TRANSPORT_SET_CHECK change how these relationships are checked:

Argument	Meaning
ts_list	Comma-separated list of tablespaces forming the candidate set
incl_constraints => TRUE	Include referential integrity constraints in the dependency check
full_check => TRUE	Check dependencies going out of the set and coming into it

The strict full check includes the outside index's dependency on the inside table. With the default full_check => FALSE, the check considers outward dependencies. A table inside the set whose index is outside is not, by itself, that default self-containment violation. The opposite arrangement—an index inside referring to a table outside—is an outward dependency.

Including constraints also detects the enabled ORDERS foreign key crossing to CUSTOMERS in APP_REF. These two directions explain why the selected options matter.

A disabled referential integrity constraint does not violate these transportability rules, and import drops it. Disabling application constraints only to obtain an empty result can change application integrity. Any redesign needs a deliberate data and constraint plan.

See Data Pump Export.

Check the candidate at the source

Connect to the source application container and confirm the destination first:

SHOW USER
SHOW CON_NAME

The documented DBMS_TTS security model requires EXECUTE_CATALOG_ROLE. The session also needs dictionary visibility in that container. This SQL*Plus block checks APP_DATA with constraints included and the full dependency check:

BEGIN
  DBMS_TTS.TRANSPORT_SET_CHECK(
    ts_list          => 'APP_DATA',
    incl_constraints => TRUE,
    full_check       => TRUE);
END;
/

SELECT violations
FROM transport_set_violations;

The slash executes the completed PL/SQL block in SQL*Plus. The SELECT reads the violations recorded by that check. Run it in the same session, immediately after the check, so the rows belong to the intended candidate and options.

A representative result has these meanings. The wording and the Oracle error numbers can vary with the arrangement and the release:

Violations
Index APP_USER.ORDERS_IX in APP_IDX points to table APP_USER.ORDERS in APP_DATA
Constraint ORDERS_CUSTOMER_FK crosses the candidate boundary to APP_REF

Read the object and tablespace names in each entry, locate the dependency, and choose a resolution. A violation remains when the data files exist and the table's rows look complete.

For this move, the intended set includes the table, its index, and the referenced customer data. Expand the candidate:

BEGIN
  DBMS_TTS.TRANSPORT_SET_CHECK(
    ts_list          => 'APP_DATA,APP_IDX,APP_REF',
    incl_constraints => TRUE,
    full_check       => TRUE);
END;
/

SELECT violations
FROM transport_set_violations;

When these are the only relevant dependencies, the expanded check returns no rows. The checker still examines the other objects and relationships in the complete candidate. Expanding the tablespace list can also bring unrelated application objects into scope. Review that scope before choosing expansion instead of a supported redesign.

Partially included partitioned tables, and tables whose LOB storage lies outside the set, need the same investigation. APP_DATA, APP_IDX, and APP_REF are not a universal transport list.

See DBMS_TTS.

Interpret an empty result

No rows means the candidate passes the dependency criteria requested by that check at that moment. An empty result does not establish source and target platform compatibility, character-set compatibility, encryption readiness, feature eligibility, a complete file list, recovery readiness, or application acceptance.

Recheck after objects or relationships change. Carry the verified tablespace list into the later transport procedure and repeat the checks that procedure requires.

Inspect current tablespace status separately:

SELECT tablespace_name, status
FROM dba_tablespaces
WHERE tablespace_name IN ('APP_DATA', 'APP_IDX', 'APP_REF')
ORDER BY tablespace_name;

A preflight can show the candidate tablespaces as READ WRITE. That status describes their current write mode. It is not a dependency or compatibility pass. The classic transport procedure introduces its read-only transition at the correct stage.

See DBA_TABLESPACES.

Compare the source with the actual target

Name both endpoints and the release documentation that applies to them. An item stays pending until evidence for the intended source, target, and object set has been checked.

Area	What to establish
Container and scope	Exact source and target services/containers; complete intended object and tablespace scope
Release and COMPATIBLE	Supported source/target combination and the compatibility needed by transported files and object features
Database character set	Same character set, or an explicitly supported binary subset/superset case with all its datatype and length-semantics conditions
National character set	Supported source/target national character-set relationship and applicable national-character datatype restrictions
Platform	Actual platform identifiers and byte order; a supported conversion method when endianness differs
Block size	Target support for each transported block size, including documented DB_nK_CACHE_SIZE requirements when it differs from the target's standard block size
Tablespace names	Existing tablespaces in the target container and a supported resolution for collisions
Object features	Supported datatypes, partitioning, LOB, XML and other features actually used by the transported objects
Encryption	Supported transport path for the encrypted data and available source/target keystore, keys and required metadata handling
Feature rights	Source export and target import permitted for the exact database offerings and deployment
Recovery and storage	Recoverable source backup, copy integrity, sufficient destination capacity, protected file paths and a tested backout approach

On the classic 19c tablespace transport path, tables with TDE column encryption are excluded. TDE-encrypted tablespaces require the documented same-endian path. Crossing endianness needs a supported decrypt, transport, and re-encrypt design. Keys alone do not remove these restrictions.

A different byte order concerns the on-disk representation. A supported conversion prepares the files for the target platform. Matching byte order does not establish character-set compatibility, and it does not remove the encryption restrictions.

Oracle's 19c offering table distinguishes transport export from some permitted imports into Standard Edition 2. Export entitlement does not follow from an available import path or from an executable being installed. Review the current offering table and the actual rights before execution. A conventional logical Data Pump move may be the appropriate alternative when transport is unavailable.

See Transporting Data and Licensing Information.

Quiz

1. A table is inside the candidate and its index is outside. Which selected option makes the strict check examine that incoming dependency?

full_check => TRUE full_check => FALSE incl_constraints => FALSE The target file name

2. Why is incl_constraints => TRUE used here?

It makes all tablespaces read only. It copies the data files. It includes referential integrity constraints in the dependency check. It proves character-set compatibility.

3. Do zero transport-set violations prove that the target's character sets and platform are compatible?

Yes, because transport set checking compares both platforms. No; source and target compatibility require their own checks. Yes, if the files have the same names. Only when the client runs on the target server.

4. New objects and relationships are added after a successful preflight. What should happen?

Treat the earlier empty result as permanent. Copy SYSTEM and SYSAUX as additional user tablespaces. Rerun the appropriate check for the complete candidate and review the changed scope. Ignore violations if the table rows can be queried.

5. Which statement correctly describes readiness for a classic transport move?

Existing data files are sufficient. An installed Data Pump utility proves all transport feature rights. Matching byte order proves all character sets and encryption are compatible. Dependency resolution, target support, feature rights, encryption and recovery arrangements must be established before scheduling the write outage.

https://www.apps-dba.com/ https://www.youtube.com/@EnhanceSkillsDaily