How to Run Oracle Data Pump Export in 26ai

Oracle AI Database 26ai uses Oracle Data Pump Export, invoked with expdp, to unload supported logical data and metadata. A job can export the supported contents of a connected pluggable database (PDB), one or more schemas, selected tables, object definitions without rows, or table rows without object definitions. The result is a proprietary Data Pump dump file set that Oracle Data Pump Import can read.

This lesson replaces the original Oracle8i exp workflow. There is no current exp80 interactive wizard, default system/manager credential, or Oracle Enterprise Manager Data Manager export screen to follow. Original Export is desupported in Oracle AI Database 26ai. Define new logical exports with expdp, preferably through a reviewed parameter file.

A Data Pump export is often called a logical backup, but its recovery promise is narrower than an RMAN backup. It does not back up data files, the control file, the SPFILE, or archived redo, and it cannot perform media or point-in-time recovery. Use Data Pump for logical portability and selective object recovery; use RMAN for physical backup and recovery.

Start with the business requirement rather than automatically selecting FULL=YES. A narrower export is usually smaller, exposes less data, and is easier to test. The selected mode also determines the privileges and metadata available to the job.

Goal	Export definition	Privilege boundary
Connected user's schema	Schema mode is the default, or use SCHEMAS=HR	The owner can export supported objects in its own schema
Another schema	SCHEMAS=APP_OWNER	Normally requires DATAPUMP_EXP_FULL_DATABASE
Named tables	TABLES=HR.EMPLOYEES,HR.DEPARTMENTS	Ownership or privileged-export rules apply
Connected database or PDB	FULL=YES	Requires privileged Export capability
Definitions without table rows	CONTENT=METADATA_ONLY	The selected mode's rules still apply
Table rows without definitions	CONTENT=DATA_ONLY	The selected mode's rules still apply

The main example uses full mode because the objective is to demonstrate an export of a database environment. In a multitenant deployment, however, FULL=YES applies to the supported contents of the database or PDB selected by the connection. It does not create one logical export containing every PDB in a CDB, and it is not a physical whole-database backup.

Every command in this lesson uses the illustrative service name pdb1. The service in the connect string selects the container in which Data Pump resolves the account, directory objects, privileges, source metadata, and control job table. A full-mode job connected to pdb1 is therefore scoped to supported pdb1 content.

Do not use an ordinary root-connected job as a shortcut for exporting all PDBs. Oracle warns that Data Pump operations are not typically needed when a client connects to the CDB root or seed. If several PDBs require logical exports, plan and test the appropriate job for each PDB service.

For an on-premises database or a user-managed OCI database host, Data Pump normally writes dump and log files through an Oracle directory object. A database administrator first creates and secures the operating-system directory. The Oracle Database processes need access to that location before the following SQL is issued in the correct PDB:

CREATE DIRECTORY dpump_dir1 AS '/u01/app/oracle/dpump';

GRANT READ, WRITE ON DIRECTORY dpump_dir1 TO dp_operator;

CREATE DIRECTORY creates only a database object that maps a name to a server path. It does not create /u01/app/oracle/dpump, change operating-system permissions, or designate a client workstation directory. The path is illustrative and must be replaced with an approved location for the actual platform.

Protect the directory and its files as production data. A full dump can contain application rows, object definitions, grants, and audit information. Limit database directory privileges, operating-system access, retention, transfer destinations, and cleanup permissions to the people and services that require them.

Use a dedicated account such as DP_OPERATOR for the privileged self-managed example. Do not run Data Pump as SYS, and do not place a database password in the shell command. The following statements show the relevant privilege boundary after the account has been created:

GRANT CREATE SESSION, CREATE TABLE TO dp_operator;
GRANT DATAPUMP_EXP_FULL_DATABASE TO dp_operator;

ALTER USER dp_operator QUOTA 100M ON users;

The Data Pump full Export role permits privileged operations outside the operator's own schema; its effect is not limited to jobs specifying FULL=YES. Grant it only when the selected export requires that reach. The user also needs sufficient quota for the Data Pump control job table and the required access to the chosen directory object.

A schema owner exporting supported objects in its own schema does not need the full Export role merely because it uses expdp. Separate routine schema exports from privileged cross-schema or full-mode jobs so that broad privileges do not become the default.

Estimate the job before allocating retention space or choosing a dump-file layout. Create a client-side parameter file named pdb1_full_estimate.par:

FULL=YES
DIRECTORY=dpump_dir1
ESTIMATE_ONLY=YES
LOGFILE=pdb1_full_estimate.log

Run it without embedding a database password:

expdp dp_operator@pdb1 PARFILE=pdb1_full_estimate.par

ESTIMATE_ONLY=YES estimates the space required without creating the requested dump set. The estimate is not a guarantee of elapsed time or final compressed size. The plan must also cover available file-system or object-storage capacity, log space, I/O, network transfer, retention, encryption, and the space required to perform a controlled import test.

Place the production job definition in a parameter file instead of constructing a long, quote-sensitive shell command. Data Pump reads the parameter file on the client, while the database server writes the dump and log files. Do not put a database password, object-storage secret, or dump encryption password in the parameter file because its contents are recorded in the Data Pump log.

The following illustrative pdb1_full.par defines a full-mode export of the connected PDB:

FULL=YES
DIRECTORY=dpump_dir1
DUMPFILE=pdb1_full_%L.dmp
LOGFILE=pdb1_full.log
JOB_NAME=PDB1_FULL_EXPORT

FILESIZE=5G
PARALLEL=4
FLASHBACK_TIME=SYSTIMESTAMP
METRICS=YES
LOGTIME=ALL
ENCRYPTION=ALL
ENCRYPTION_MODE=PASSWORD
ENCRYPTION_PWD_PROMPT=YES

• %L creates a scalable numbered file sequence. Unlike the two-digit %U range of 01 through 99, %L can continue with larger variable-width numbers.
• FILESIZE=5G limits each dump file. When more space is needed, the %L template permits Data Pump to create another file.
• JOB_NAME supports later attachment. It must not conflict with an existing table or view in the job owner's schema.
• PARALLEL=4 is illustrative. Parallel execution is an Enterprise Edition capability, and the appropriate degree depends on licensed features, CPU, storage, object sizes, access methods, and measured workload impact.
• FLASHBACK_TIME=SYSTIMESTAMP requests a logically consistent export at the corresponding SCN, subject to Flashback Query and undo availability. It does not invoke Flashback Database or create physical recovery capability.
• METRICS=YES adds object and elapsed-time information, while LOGTIME=ALL timestamps log messages.
• ENCRYPTION_PWD_PROMPT=YES obtains the dump-file encryption password interactively. Preserve that password securely for the corresponding Import operation and verify the applicable edition, licensing, and key-management requirements.

Tune optional settings for the actual environment. A copied PARALLEL=4 or FILESIZE=5G value is not a capacity plan. Likewise, do not add REUSE_DUMPFILES=YES merely for convenience; its default of NO helps prevent accidental replacement of an earlier dump.

A Data Pump dump set can contain one file or many files. The files are one logical unit: an import must be able to locate every file actually used by the export. Capture the final dump-file list from the log rather than assuming that every possible name generated by the template exists. When files are copied to another server or object store, transfer the complete set and verify its integrity after transfer.

Use a naming convention that identifies the source PDB, export scope, generation, and sequence without exposing sensitive business details. Keep the log with the corresponding dump generation, but protect it because object names, filters, paths, and diagnostic messages can also reveal operational information. Record the source release, compatible setting, time-zone file version, parameter file, encryption method, and retention expiration in the organization's backup or migration inventory.

Do not move or delete files that belong to an active or restartable job. After successful completion, retain the dump set according to policy and keep the encryption password or wallet material in a separate approved secrets system. A perfectly preserved encrypted dump is unusable if its decryption material is lost; an accessible password defeats the protection if it is stored beside the dump files.

Rotation must also protect the last known usable generation. Create the new export under unique names, validate it, and complete the controlled import test before expiring the previous generation. This sequencing is safer than overwriting files in place with REUSE_DUMPFILES=YES.

Start the job from a host with an appropriate Data Pump client:

expdp dp_operator@pdb1 PARFILE=pdb1_full.par

The client prompts for the database credential and, for the preceding encrypted job, the dump encryption password. An approved wallet, external authentication method, or other protected credential mechanism can replace interactive database authentication. Never use a pattern such as dp_operator/secret@pdb1 because the credential can leak through scripts, process listings, shell history, or logs.

Data Pump's interactive-command mode is different from the original Export wizard. Pressing Ctrl+C while attached suspends terminal logging and displays the Export> prompt; it does not automatically terminate the server-side job. Useful controls include:

Command	Effect
STATUS=60	Displays status and requests periodic status output every 60 seconds
EXIT_CLIENT	Detaches the client while leaving the job running
STOP_JOB	Performs an orderly stop that can preserve restart capability
STOP_JOB=IMMEDIATE	Stops promptly; incomplete work might be repeated after restart
START_JOB	Restarts the stopped job to which the client is attached
KILL_JOB	Terminates the job and discards its restart capability

After detaching, the same job owner can attach by using the name in the parameter file:

expdp dp_operator@pdb1 ATTACH=PDB1_FULL_EXPORT

The attached client can then issue commands such as:

Export> STATUS
Export> STOP_JOB
Export> START_JOB
Export> CONTINUE_CLIENT

The control job table and dump file set must remain intact for a stopped job to restart. Do not move, rename, delete, or modify files while an active or restartable job depends on them.

The appearance of one or more .dmp files does not prove that the requested export completed cleanly or can be imported. Validate the result as an operational package:

1. Check the client process exit code.
2. Read the entire log for errors, warnings, excluded content, and the actual dump files used.
3. Compare logged objects and row processing with the approved export scope.
4. Verify the ownership, permissions, sizes, encryption, retention, and availability of every file in the dump set.
5. Confirm target compatibility, including database release, VERSION, character sets, time-zone files, options, and encryption material.
6. Perform a controlled test import into a suitable nonproduction environment.

Exit code	Meaning
0	Completed successfully without reported errors
5	Completed, but encountered errors that require review
1	Encountered an irrecoverable failure

While a job exists, its state can be inspected through USER_DATAPUMP_JOBS. An appropriately authorized administrator can use DBA_DATAPUMP_JOBS for broader visibility:

SELECT job_name,
       operation,
       job_mode,
       state,
       degree,
       attached_sessions
FROM   user_datapump_jobs
ORDER  BY job_name;

A test that only generates DDL with Import's SQLFILE option can help review metadata, but it does not prove that all table data and application dependencies load successfully. A representative import and application-level verification provide stronger evidence that the logical export is usable.

A schema owner can export its own supported objects without receiving the full Export role. For example, HR can run a schema-mode job in pdb1:

expdp hr@pdb1 DIRECTORY=dpump_dir1 DUMPFILE=hr_%L.dmp LOGFILE=hr_export.log SCHEMAS=HR

The same owner can select named tables:

expdp hr@pdb1 DIRECTORY=dpump_dir1 DUMPFILE=hr_tables.dmp LOGFILE=hr_tables.log \
TABLES=EMPLOYEES,DEPARTMENTS

Use CONTENT=METADATA_ONLY when the requirement is to capture supported definitions without table rows. Use CONTENT=DATA_ONLY only when the target already has the required structures and the data-only design has been tested. Filters such as INCLUDE, EXCLUDE, and QUERY should normally be placed in a parameter file because their quoting and dependencies require careful review.

OCI does not describe one Data Pump storage model. A database on a user-managed OCI compute host can use a directory object and controlled server-side storage. Autonomous AI Database 26ai can instead write dump files directly to supported cloud object storage by using a database credential, a supported URI, and the CREDENTIAL parameter.

The following sanitized parameter-file pattern targets OCI Object Storage:

FULL=YES
CREDENTIAL=OCI_OBJECT_STORAGE_CRED
DUMPFILE=https://<namespace>.objectstorage.<region>.oci.customer-oci.com/n/<namespace>/b/<bucket>/o/pdb1_full_%L.dmp
FILESIZE=5G
PARALLEL=4
DIRECTORY=DATA_PUMP_DIR
LOGFILE=pdb1_full_object_storage.log
ENCRYPTION_PWD_PROMPT=YES

Replace every placeholder and create the database credential through the current service-approved process. Do not place a tenancy secret, token, private key, password, or pre-authenticated request URL in the lesson or parameter file. In this direct export, DUMPFILE identifies object-storage URIs, while DIRECTORY identifies the database directory used for the log file.

Autonomous privileged exports use the service's documented role, such as DATAPUMP_CLOUD_EXP, rather than assuming the self-managed DATAPUMP_EXP_FULL_DATABASE model. Bucket creation, URI format, network access, credential ownership, parallelism, log retrieval, retention, and cleanup must follow the documentation for the selected service and object-storage provider.

Oracle AI Database 26ai uses trailer blocks by default for newly created Data Pump dump files. The VERSION parameter controls compatible metadata and can affect the dump block format when a pre-26ai-compatible value is selected. Choose VERSION from the intended import target and the documented restrictions, not simply from the release of the Export client.

The current %L substitution variable supports a much larger generated sequence than %U. In addition, when REUSE_DUMPFILES=YES is used in 26ai, Export verifies that an existing target is a Data Pump dump file before overwriting it. That safeguard does not make overwriting desirable; use unique names and preserve required generations according to policy.

A full-mode export can include unified audit trail data. If an organization chooses EXCLUDE=AUDIT_TRAILS, Oracle recommends archiving the audit trail first. The decision affects security, compliance, dump size, and the contents available during Import, so it belongs in the approved export design rather than an ad hoc command change.

A secure, validated Data Pump export can provide valuable logical portability. It can preserve supported schema definitions, copy application data, support migrations, and make selected object recovery more practical. Its usefulness depends on correct scope, compatible metadata, complete dump files, protected encryption material, and a tested Import process.

It cannot restore a lost data file, recover a control file, apply archived redo, repair a physical block, or recover the database to an arbitrary point in time. Build and test the RMAN strategy needed for physical loss, then add Data Pump exports where logical movement or selective object handling offers a separate benefit.

Click the Quiz link below to check your understanding of the Export utility.
Running Export - Quiz
In the next lesson, you will learn to use the Export utility to perform a complete backup of your database.