Data Pump Export/Import, Transportable Tablespaces, Network Import, and Migration Workflows

Learning outcomes

ServiceHub must move one application schema into a fresh PDB while keeping the source intact for rollback. Copying datafiles would also move database-level physical state the team does not want; hand-written CREATE/INSERT scripts would miss grants, constraints, sequences and metadata. Oracle Data Pump is a server-side logical metadata/data movement engine exposed by expdp/impdp.

1. Data Pump client and server roles

expdp/impdp are command-line clients. The actual Data Pump job runs in the database server and reads/writes server-accessible files through a database DIRECTORY object. The DIRECTORY object maps a database name to an OS path and has database privileges such as READ/WRITE.

SELECT directory_name,directory_pathFROM dba_directoriesWHERE directory_name='DATA_PUMP_DIR';

The path proves where the server writes logs/dump files. The client working directory is irrelevant.

2. Build an isolated ServiceHub migration schema

BEGIN EXECUTE IMMEDIATE 'DROP USER sh28_src CASCADE';EXCEPTION WHEN OTHERS THEN IF SQLCODE != -1918 THEN RAISE; END IF; END;/BEGIN EXECUTE IMMEDIATE 'DROP USER sh28_dst CASCADE';EXCEPTION WHEN OTHERS THEN IF SQLCODE != -1918 THEN RAISE; END IF; END;/CREATE USER sh28_src NO AUTHENTICATION  DEFAULT TABLESPACE users  QUOTA 50M ON users;CREATE USER sh28_dst NO AUTHENTICATION  DEFAULT TABLESPACE users  QUOTA 50M ON users;GRANT CREATE TABLE,CREATE SEQUENCE,CREATE VIEW TO sh28_src;GRANT CREATE TABLE,CREATE SEQUENCE,CREATE VIEW TO sh28_dst;CREATE TABLE sh28_src.work_orders (  work_order_id NUMBER PRIMARY KEY,  status_code VARCHAR2(12) NOT NULL,  amount NUMBER(12,2) NOT NULL);CREATE SEQUENCE sh28_src.work_order_seq  START WITH 1001 CACHE 20;INSERT INTO sh28_src.work_orders VALUES(1001,'OPEN',125.50);INSERT INTO sh28_src.work_orders VALUES(1002,'CLOSED',80.00);CREATE VIEW sh28_src.open_orders_v ASSELECT work_order_id,amountFROM sh28_src.work_ordersWHERE status_code='OPEN';GRANT SELECT ON sh28_src.open_orders_v TO servicehub_app;COMMIT;

3. Baseline the source before export

SELECT COUNT(*) row_count,       SUM(amount) total_amount,       MIN(work_order_id) min_id,       MAX(work_order_id) max_idFROM sh28_src.work_orders;SELECT sequence_name,last_number,cache_sizeFROM dba_sequencesWHERE sequence_owner='SH28_SRC'  AND sequence_name='WORK_ORDER_SEQ';SELECT object_type,COUNT(*) object_countFROM dba_objectsWHERE owner='SH28_SRC'GROUP BY object_typeORDER BY object_type;SELECT grantee,privilegeFROM dba_tab_privsWHERE owner='SH28_SRC'  AND table_name='OPEN_ORDERS_V';

These are migration acceptance baselines, not merely inventory trivia. Sequence/grant state can break an application even when every table row copied correctly.

4. Deliberate failure: use an OS path where Data Pump expects a DIRECTORY object

expdp system@//localhost:1521/FREEPDB1   schemas=SH28_SRC   directory=NO_SUCH_DIR   dumpfile=sh28_src.dmp   logfile=sh28_src_export.log# Expected:# ORA-39087: directory name NO_SUCH_DIR is invalid

The repair is to use a real database DIRECTORY object and grant the Data Pump principal access to it—not to chmod a random client directory.

5. Free-compatible schema export

expdp system@//localhost:1521/FREEPDB1   schemas=SH28_SRC   directory=DATA_PUMP_DIR   dumpfile=sh28_src.dmp   logfile=sh28_src_export.log   compression=METADATA_ONLY   parallel=1   reuse_dumpfiles=NO

Mandatory lab deliberately uses PARALLEL=1 and metadata-only compression. Current Data Pump documentation restricts data compression (ALL/DATA_ONLY) to Enterprise Edition with Advanced Compression, while the current licensing matrix separately describes option inclusion by offering. Production must satisfy both utility restrictions and licensing entitlement. Do not infer that a parameter is legal merely because it parses.

6. Import into a different schema

impdp system@//localhost:1521/FREEPDB1   schemas=SH28_SRC   directory=DATA_PUMP_DIR   dumpfile=sh28_src.dmp   logfile=sh28_dst_import.log   remap_schema=SH28_SRC:SH28_DST

Because SH28_DST exists with quota, objects can be remapped predictably. Review the import log for skipped object types, errors and transformed metadata; “Job successfully completed” is not enough business validation.

7. Prove the target beyond connectivity

SELECT COUNT(*) row_count,       SUM(amount) total_amount,       MIN(work_order_id) min_id,       MAX(work_order_id) max_idFROM sh28_dst.work_orders;SELECT object_type,COUNT(*) object_countFROM dba_objectsWHERE owner='SH28_DST'GROUP BY object_typeORDER BY object_type;SELECT owner,object_name,object_typeFROM dba_objectsWHERE owner='SH28_DST'  AND status='INVALID';SELECT sequence_name,last_number,cache_sizeFROM dba_sequencesWHERE sequence_owner='SH28_DST'  AND sequence_name='WORK_ORDER_SEQ';SELECT grantee,owner,table_name,privilegeFROM dba_tab_privsWHERE owner='SH28_DST'ORDER BY table_name,grantee,privilege;

Compare these with the captured source baseline. Sequence LAST_NUMBER is cache/high-water metadata, not a promise that every preceding number was used; the migration test is whether the next generated key is safe and application semantics remain correct.

8. NETWORK_LINK import eliminates the intermediate dump file

With a database link from the target to the source, impdp NETWORK_LINK=... pulls metadata/data directly over the database connection. This can simplify storage logistics but couples migration speed/consistency to a live source/network/database link and its privileges.

-- On target, create a least-privilege DB link according to policy.-- Then from the target host:impdp system@//target-host:1521/TARGETPDB   network_link=SH28_SOURCE_LINK   schemas=SH28_SRC   remap_schema=SH28_SRC:SH28_DST   logfile=sh28_network_import.log

No dump file is specified for a pure network import. Version restrictions and source/target feature compatibility still apply.

9. Transportable tablespaces move datafiles; Data Pump moves metadata

For very large user tablespaces, transportable tablespace/full transportable workflows avoid unloading/reloading every row. First prove the set is self-contained, place the source tablespaces into the required transport state, export metadata, copy/convert datafiles, then import metadata at the target.

BEGIN  DBMS_TTS.TRANSPORT_SET_CHECK(    ts_list          => 'SH28_TTS',    incl_constraints => TRUE  );END;/SELECT *FROM transport_set_violations;

The lab does not create/copy a real datafile because paths/platforms differ. A non-empty violation list means the proposed transport set is not self-contained.

10. Endianness determines physical conversion choices

SELECT platform_nameFROM v$database;SELECT platform_id,platform_name,endian_formatFROM v$transportable_platformORDER BY platform_name;

RMAN CONVERT DATABASE requires source and target platforms with the same endian format. For different endianness, use logical Data Pump or transport individual tablespaces/datafiles with RMAN conversion as supported. Even same-endian full database transport has its own conversion requirements; never reduce cross-platform migration to file copy.

11. 26ai dump-file format is itself a migration consideration

26ai Data Pump defaults to a trailer-block dump format that supports object-store workflows. A default 26ai dump can be readable only by 26ai or later Data Pump servers. If the target is an older release, choose a supported VERSION/workflow and test that every required object/data type can be represented. A logical downgrade is not guaranteed just because Data Pump has a VERSION parameter.

12. Logical versus physical migration

Pick from RTO/downtime, source/target versions, platform/endian, data volume, remapping requirements and rollback—not tool familiarity.

13. Cleanup and production judgment

DROP USER sh28_dst CASCADE;DROP USER sh28_src CASCADE;-- Remove sh28_src.dmp/logs from DATA_PUMP_DIR only after-- validation/retention policy allows it.

Data Pump is logical migration, not backup/DR. Validate data, objects, grants, jobs, sequences, invalids and application behavior. Use transportable/RMAN methods for large physical datasets only when self-containment, platform/endian and encryption rules are satisfied.

No mandatory lab uses Data Pump data compression, parallel workers greater than one, or dump encryption. Production compression/encryption requires the current utility restrictions plus Advanced Compression/Advanced Security entitlement as applicable. Lesson 4 reduces application downtime for schema/table changes without moving the whole database.

Authoritative references

• Oracle Data Pump Overview — logical migration and 26ai dump format
• Data Pump Export Parameters — compression/encryption/export controls
• Data Pump Full/Transportable Export — full transportable requirements
• Transporting Data — network/full transportable workflows
• Licensing Information — compression/security/cross-platform feature entitlements