Transportable Tablespaces in Oracle 26ai - Exercise

Objective: Write the SQL and Data Pump parameters needed to create a locally managed tablespace, validate its transport set, and prepare its metadata and datafiles for transport.

Background and Overview

A reporting application needs a copy of a dataset stored in NEWSPACE. First design the source tablespace. Then assume application objects have been loaded into it and prepare a conventional transportable-tablespaces export from the source PDB.

This is a written, unscored exercise. The form displays your answer beside a reference solution; it does not execute SQL, transfer files, or connect to a database. No simulation or screenshot is required.

Scenario and Prerequisites

Use self-managed Oracle 26ai test PDBs with services srcpdb and dstpdb. The administrator has appropriate creation, tablespace-management, package-execution, and catalog privileges.
NEWSPACE is an unused source name. Use a smallfile tablespace with one new file: /u02/oradata/srcpdb/newspace01.dbf. The approved directory exists, is writable by the database, and has sufficient capacity. This is an illustrative server path.
The source file begins at 200M, grows automatically in 20M increments, and has a 2G autoextension ceiling. Use local extent management, uniform 20M extents, and automatic segment space management (ASSM).
After creation, assume a separate loading process populates the application objects. Do not invent a loading script. Relevant tables, indexes, LOBs, and dependencies must be checked before transport.
A database DIRECTORY object named DP_DIR is already configured in each PDB with the required grants and server filesystem access. The Data Pump account tts_admin has the required export role at the source and import role at the target.
Assume compatible source and target platforms with matching endianness, supported object types, compatible character sets and block sizes, and no encryption-key transfer requirement for this lab. A real migration must establish these conditions explicitly.

The file sizes are teaching values. A 20M extent size and a 20M autoextension increment are separate settings even though their numbers match.

Your Tasks

Write the source CREATE TABLESPACE statement using all the specified storage settings.
After the assumed data load, call DBMS_TTS.TRANSPORT_SET_CHECK for NEWSPACE with constraints included and a full check. Query TRANSPORT_SET_VIOLATIONS and explain what to do if rows are returned.
Write the statement that changes NEWSPACE to READ ONLY and a query that verifies its status. State how long you will maintain this source state for the conventional export and copy operation.
Write the contents of newspace_export.par using DP_DIR, dump file newspace_meta.dmp, log file newspace_export.log, NEWSPACE as the transport set, and strict transport checking. Retain the default eligible metadata selection; do not add legacy trigger, constraint, or grant switches.
Write the operating-system command that starts Data Pump Export through srcpdb using that parameter file. Omit the password so the utility prompts for it.
Write a query that inventories NEWSPACE datafiles. In a short handover note, identify what must be transferred and describe the target preparation, import, validation, and final mode selection needed to complete transport.

Hints

Use expdp and TRANSPORT_TABLESPACES. A transport metadata dump does not contain the datafile contents. The parameter file is read by the Data Pump client; DP_DIR identifies a server-side location for the dump and log.

Review Transportable Tablespaces in Oracle 26ai for the workflow. Label SQL, parameter-file contents, and operating-system commands separately in your answer.

Transportable Tablespaces in Oracle 26ai - Exercise

Background and Overview

Scenario and Prerequisites

Your Tasks

Hints

Submit Your Answer