Oracle Data Pump Export and Import in Oracle AI Database 26ai

Logical export within a backup strategy

A logical backup records exported object definitions and data. It can help reconstruct a table or move a schema into another database. It does not preserve the physical database files, control file, server parameter file, and redo needed for media recovery. Keep RMAN backups and archived redo appropriate to the recovery plan. A successful Data Pump job does not demonstrate that a damaged database can be restored.

Consider two incidents. If an application loses a reference table, a tested export may provide a useful copy for investigation and controlled repair. If storage containing database files fails, importing that table cannot bring the database back online. The recovery plan must distinguish those outcomes before an incident occurs. A selected-row export also intentionally leaves some rows behind, so it cannot be advertised as a complete backup of the original table.

Define an acceptance test before exporting. For a migration, that might mean loading the selected tables into a validation PDB and running an application smoke test. For a logical recovery copy, record the export's data boundary and the process for reconciling later changes. Retain the dump files, logs, parameter file, and validation notes together. The files are easier to use when their purpose is documented rather than inferred from a filename.

Choose the scope and PDB service

Scope determines which objects the job selects. Use TABLES for a focused table operation and SCHEMAS for a schema operation. Metadata-only exports can support definition review, but they omit table rows. A full-mode export connected to a PDB is still a logical operation in that connected container; do not mistake FULL=YES for an RMAN backup of an entire CDB.

Verify the source and destination service names before starting. An account named PETSTORE can exist in more than one PDB, and connecting to the wrong service can produce a perfectly valid dump of the wrong data. Record the intended PDB, schema, table list, and filtering rule in the job notes. In the examples below, source_pdb_service and validation_pdb_service are placeholders that must be replaced with real, verified connect identifiers.

Work within the account's authorized scope. Exporting another owner's objects or remapping schemas may require additional privileges. Do not solve every permission problem by granting a broad administrative role. Have the DBA identify the operation and grant the access it actually needs.

Prepare server storage and directory access

Data Pump clients start and monitor jobs, while the database server performs the work. A database directory object identifies a server-side location for dump and log files. It is not the directory containing the client parameter file. The database service also needs operating-system access to the underlying filesystem; a database grant alone cannot make an inaccessible server path usable.

For a self-managed example, assume that a DBA has created the server directory /u02/dpump with suitable ownership and capacity. Connected to the intended PDB, the DBA could define the directory object and grant access as follows. These are illustrative preparation statements, not commands executed for this lesson. Managed cloud services may require a different storage arrangement.

CREATE DIRECTORY dpump_dir AS '/u02/dpump';
GRANT READ, WRITE ON DIRECTORY dpump_dir TO petstore;

Prepare corresponding storage and directory access at the destination. Copy the complete dump-file set there before a file-based import. Use distinct names for successive exports, check available space, and protect dump files according to the sensitivity of their contents. A narrowly filtered export can still contain personal or commercially sensitive data.

Export selected pet-store rows

Assume that PETSTORE owns a conventional relational table named PRODUCT_OBJ_TABLE, with a PET_FLAG column. Its historical name does not imply that every Oracle object-table or nested-table configuration supports this example. The business requirement is to copy only rows whose flag is 'Y'. First examine what the predicate means using a SQL query:

SELECT COUNT(*) AS selected_rows
FROM petstore.product_obj_table
WHERE pet_flag = 'Y';

This count is a planning check, not evidence of a completed export. NULL flags do not satisfy the equality predicate. Confirm with the application owner that this matches the intended selection. If the source remains active, the count can change before export begins; use a coordinated snapshot when an exact comparison is required.

Save the following settings in a client-side parameter file called petstore_selected.par. The table-qualified QUERY makes the row-selection rule explicit and avoids relying on a WHERE clause that must apply to every table in a larger job.

DIRECTORY=DPUMP_DIR
DUMPFILE=petstore_selected_%U.dmp
LOGFILE=petstore_selected_export.log
TABLES=PETSTORE.PRODUCT_OBJ_TABLE
QUERY=PETSTORE.PRODUCT_OBJ_TABLE:"WHERE PET_FLAG = 'Y'"
FILESIZE=1G
FLASHBACK_TIME=SYSTIMESTAMP

Start the illustrative export from the client environment where the parameter file is available:

expdp petstore@source_pdb_service PARFILE=petstore_selected.par

Enter the password when prompted. No password is stored in this example. Confirm that the installed client and database combination is supported for the intended operation. Inspect the export log after completion, including errors and the reported table processing. Do not infer success merely because a dump file appeared in the directory.

Understand QUERY and snapshot consistency

The Data Pump QUERY parameter filters rows. A table-qualified clause applies to that table; an unqualified clause must be valid for every table affected by the job. Parameter files simplify quoting compared with shell command lines. The old page's backslash convention was not a universal rule for every operating system.

Data Pump uses the external-table access method for filtered export. Do not carry over the original Export restriction that row filters work only in table mode. However, QUERY cannot be combined with CONTENT=METADATA_ONLY, ESTIMATE_ONLY, or TRANSPORT_TABLESPACES. Check the current parameter reference when combining options, rather than treating historical restrictions as current requirements.

Filtering one table does not automatically select corresponding parent or child rows elsewhere. Suppose selected products refer to category records. A usable test copy may require those categories as well. Conversely, dependent rows might refer to products deliberately omitted from the dump. Plan the related-table selection and validate constraints after import. A technically successful extraction can still be incomplete for the application's needs.

The example uses FLASHBACK_TIME=SYSTIMESTAMP for a consistent export boundary. An alternative is a recorded FLASHBACK_SCN; specify one approach, not both. Flashback query depends on available undo and the required access. For a reproducible comparison, retain the chosen SCN and compare source data at that same snapshot while it remains available. A live count taken later is not necessarily comparable.

This export consistency setting does not recover lost data files or capture every future transaction. Label the dump with its snapshot boundary and selection rule. If the application continues writing, decide how those later changes will be handled before using the export as a migration or recovery source.

Manage dump files and performance

The example uses a %U filename template and FILESIZE=1G to let Data Pump create multiple dump members. Retain every member used by the job. Copying only the first file can leave an unusable set. A practical transfer record lists each filename, byte size, and checksum, alongside the export log. Verify the copied files before starting import.

Choose file size according to storage and transfer requirements. There is no reason to repeat the old article's blanket two-gigabyte limit as a modern Oracle constraint. Also avoid treating a reusable dump filename as an invitation to overwrite an earlier recovery copy. Establish retention and naming rules before scheduling repeated exports.

Parallelism is an environment-specific tuning decision. Start with a manageable job, measure elapsed time and resource use, and increase concurrency only when the edition, storage, and workload support it. Additional workers do not guarantee proportional speed improvements. A row filter can require significant source processing even when the final dump contains relatively few rows. Plan a suitable export window and monitor the effect on the source application.

Preview the import before loading data

Prepare a separate validation PDB with the intended PETSTORE account, directory object, privileges, tablespace capacity, and quota. These examples assume the target table does not already exist. Review the DDL before loading the dump, especially tablespace references and object dependencies. The following command writes a SQL preview:

impdp petstore@validation_pdb_service
  DIRECTORY=DPUMP_DIR
  DUMPFILE=petstore_selected_%U.dmp
  SQLFILE=petstore_preview.sql
  LOGFILE=petstore_preview.log

The displayed lines are one command, wrapped for readability. Enter it as one line or put the parameters in an import parameter file. SQLFILE writes the SQL that Import prepares; it does not apply that SQL or load rows. Inspect the resulting server-side file and preview log. This catches planning issues, but it is not proof that a real import will complete or that the application will work.

If destination ownership or storage differs, review REMAP_SCHEMA and REMAP_TABLESPACE with the DBA. Prepare the target schema and its access rather than assuming remapping provisions everything. Examine schema references in application code and object definitions as well as the table owner. Use INCLUDE and EXCLUDE for object selection; they serve a different purpose from a row predicate.

Import into an empty validation destination

After reviewing the preview and preparing the destination, a focused table import could use the following settings. Again, this is an illustrative command and its wrapped lines form one invocation:

impdp petstore@validation_pdb_service
  DIRECTORY=DPUMP_DIR
  DUMPFILE=petstore_selected_%U.dmp
  TABLES=PETSTORE.PRODUCT_OBJ_TABLE
  LOGFILE=petstore_selected_import.log

Check the destination immediately before execution. If the table already exists, decide its treatment explicitly. TABLE_EXISTS_ACTION offers behaviors including skipping, appending, truncating, and replacing. Truncation and replacement can remove existing data or objects; they are not routine fixes for an unexpected target collision. Appending can introduce duplicates. The safest learning setup is a deliberately prepared empty destination with a reviewed import plan.

Keep the import log with the dump set. Investigate failed objects and warnings rather than treating the last message as the entire result. If a test fails, preserve the evidence and correct the cause before repeating it. Document whether the next attempt uses a fresh target, corrected grants, changed mapping, or a new export, so the final outcome remains understandable.

Verify rows, objects, and application behavior

Verification should answer the original business question. For the pet-store example, count destination rows and check for rows that violate the filter:

SELECT COUNT(*) AS imported_rows
FROM petstore.product_obj_table;

SELECT COUNT(*) AS unexpected_rows
FROM petstore.product_obj_table
WHERE pet_flag <> 'Y' OR pet_flag IS NULL;

Expect no predicate violations in a fresh destination containing only this export. Compare the imported count with the documented source snapshot or export evidence. Matching counts alone are insufficient: review representative keys and values, required indexes, constraint status, invalid objects, and an application-level operation. Include any related tables needed for the test. Record observed results, rather than copying expected results into a verification report.

Review optimizer statistics after import

Optimizer statistics help Oracle estimate data volumes and choose execution plans. A filtered destination can have a different distribution from the source, so imported statistics may need review. Assess representative queries and gather appropriate statistics with DBMS_STATS where needed, taking account of locked statistics and the environment's statistics policy.

Transporting optimizer statistics through a DBMS_STATS staging table is a separate procedure from exporting application tables. Reusing statistics may help a controlled performance investigation, but it does not validate an import. Avoid carrying forward the legacy rule-based optimizer discussion as current tuning guidance.

Keep a usable logical recovery record

A small rehearsal with explicit acceptance criteria

Before expanding this example to a whole schema, write a short rehearsal plan. Name the source PDB, the validation PDB, the expected table owner, and the person who can confirm the business meaning of PET_FLAG. State whether the destination contains only exported data or also has independently maintained records. That distinction determines whether a count comparison is meaningful and whether an existing-table action is appropriate.

Separate preparation, execution, and acceptance in the record. Preparation establishes access, storage, dump availability, and the intended destination. Execution records the parameter files and actual export and import logs. Acceptance records the checks performed and their observed outcomes. This structure helps another DBA repeat the operation without relying on the original operator's recollection. Include the client and server versions and any environment-specific adaptations made to the examples.

Use an explicit stop condition. If the destination service is wrong, a dump member is missing, or the import reports an unresolved object failure, do not declare the copy ready for the application. Diagnose the problem against the recorded plan. A missing directory grant calls for an access correction; an inconsistent related-table selection may call for a revised export. Repeating the same command without changing the cause can make the evidence harder to interpret.

Finish the rehearsal with a short handover: which dataset was copied, how it was selected, when its snapshot was taken, which objects were validated, and which limitations remain. For example, a successful selected-product import might support testing product display while leaving order-processing tests outside its scope. State that boundary plainly. Then confirm that the physical backup and archived-redo strategy still meets the recovery objectives for the original database.

Retain the selection rule, source snapshot, complete dump set, checksums, logs, and tested import procedure. Keep that logical recovery record alongside the physical recovery strategy and its verification evidence. The next lesson examines duplex or multiple archive logs, another part of protecting the redo needed for recovery.

Oracle documentation

• 26ai changes and original Export desupport
• Data Pump overview and server-side operation
• Export parameters, QUERY, and flashback settings
• Import parameters, SQLFILE, and existing tables
• Transporting optimizer statistics
• Physical backups and logical exports