Oracle DBA Lesson 86 Part 1 — Export an owned schema as a server job

Oracle Data Pump export creates a binary dump file set containing table data, object definitions and control information. A database job performs the work. The expdp client starts or attaches to that job; dump and log files are written on the database server.

Oracle DBA Lesson 86 Part 1 — Export an owned schema as a server job

This is the first of two parts. Part 1 covers export scope, server file access, detaching, inspection and completion. Part 2 reviews import DDL and validates a remapped copy.

Follow the work and its files

1. The client connects to the selected database service as APP_USER.
2. The export job reads the owned schema's objects and data. The original tables remain in the database.
3. The server writes the dump file and export log under a database DIRECTORY object.
4. The completed logical export can be consumed by Data Pump import. Keep a separate, tested backup and recovery plan for database recovery.

DIRECTORY=DP_DIR names a database object, not a client folder. An administrator maps DP_DIR to an existing protected server folder such as /u02/dpump. Two access layers must work:

Layer	Requirement
Database access	The exporting user has the necessary READ and WRITE grants on DP_DIR.
Server filesystem	The database operating-system account can access the existing directory and create the files, with sufficient free space.

Creating a directory object does not create the physical directory or override operating-system permissions. Restrict directory access because the dump can contain application data. The client's working directory does not determine the dump destination. Parameter files, when used, are client-side inputs; this does not change the location of dump and log output.

See Data Pump overview and CREATE DIRECTORY.

Prepare the owned-schema export

Use a schema you own, with CREATE SESSION, CREATE TABLE, sufficient tablespace quota for the job's control table, and delegated access to the existing directory. The control table tracks the job and is named after it, so the job name must also avoid an existing table or view name in the schema. Exporting your own schema does not require the full-database export role. Cross-schema export has additional privilege requirements and is outside this scope.

Keep the source quiescent: no concurrent writers or DDL changes. If the export needs a single consistent data snapshot while writers continue, plan FLASHBACK_SCN or FLASHBACK_TIME, the required undo, and the operational conditions before starting. These mutually exclusive options establish a consistent data SCN. They do not replace a backup and recovery strategy or permit arbitrary concurrent structural changes.

Confirm the intended service, container and account. Check free space and unused dump and log names. Use new names for every retained run. Existing dump files are rejected by default; existing log files can be overwritten. Do not enable REUSE_DUMPFILES merely to bypass a collision.

Start a traceable run

Run from the operating-system shell with an appropriate Oracle client environment and a resolved service alias:

expdp app_user@APP_SERVICE \
  schemas=APP_USER directory=DP_DIR \
  dumpfile=app_user_01.dmp \
  logfile=app_user_01.log \
  job_name=APP_EXPORT_01

Supply the password at the prompt. The command contains no password.

Parameter	Purpose
app_user@APP_SERVICE	Connect as the owner to the intended database service. APP_SERVICE is the connection alias.
SCHEMAS=APP_USER	Select the connected user's owned schema. The dump contains exportable objects and data within that scope. Cross-schema dependencies may need additional planning.
DIRECTORY=DP_DIR	Choose the mapped server directory for the files shown.
DUMPFILE=app_user_01.dmp	Name the binary dump. Do not open or modify it as a text file.
LOGFILE=app_user_01.log	Name the server log recording execution, object processing and errors.
JOB_NAME=APP_EXPORT_01	Give the database job an identity for inspection and reattachment.

The folder and grants are prerequisites, not created by this command. Additional compression, parallelism and encryption options have release, edition or licensing conditions; they are not enabled here. See Data Pump Export and licensing information.

Detach, inspect and reattach

While the client is displaying export progress, press Control C to enter interactive command mode. This switches to the Export> prompt; it does not itself request stopping the database job.

Export> EXIT_CLIENT

EXIT_CLIENT exits or detaches the client and leaves the job running. It stops terminal logging, while the server log remains associated with the job. If the intention is to stop work for later restart, the interactive STOP_JOB command requests that behavior. Detaching is separate from stopping or killing a job.

In SQL*Plus, connected as the same user to the same database service, inspect:

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

A running, detached job can show:

JOB_NAME	OPERATION	JOB_MODE	STATE	ATTACHED_SESSIONS
APP_EXPORT_01	EXPORT	SCHEMA	EXECUTING	0

EXECUTING describes the server job's state. ATTACHED_SESSIONS=0 counts attached client sessions; it does not mean no server workers exist or that the export stopped. A very small job may finish before you query it.

Reattach to a job that still exists, using its owner, service and saved job name:

expdp app_user@APP_SERVICE attach=APP_EXPORT_01

At the interactive prompt, STATUS requests status. After an unexpected client interruption, inspect the existing job and its log before submitting another export. Do not manually drop its control table to clear the inventory.

See USER_DATAPUMP_JOBS and DBA_DATAPUMP_JOBS.

Establish completion and retain the artifacts

Read the final completion message and review the log for errors, warnings and skipped objects. Account for the intended objects and exported row counts against the quiescent source inventory. A log file's mere existence does not establish successful completion.

The control table is normally dropped on successful completion, unless retention such as KEEP_MASTER=YES is selected. The job view can also expose retained control tables for jobs that are not currently running. Therefore, absence from the view alone is insufficient evidence of success. See Data Pump overview.

Retain a small export record:

Record	Value to capture
Source service and schema	APP_SERVICE, APP_USER
Export time and job	Actual time, APP_EXPORT_01
Directory and server files	DP_DIR, actual mapped path, dump and log names
Completion	Final message and resolved errors and warnings
Scope and accounting	Intended objects and reconciled exported counts

Protect the binary dump and its log. Leave the source schema intact and retain the dump for Part 2. Do not move, delete or alter Data Pump files during a running job. After copy validation and retention approval, the directory owner can remove the designated artifacts; no source-schema deletion is needed.

Quiz

1. Does copying the expdp client to a laptop make the dump land on that laptop?

Yes, always. No. The database job writes it using the server directory object. Only if the client folder is empty. Only if no log is requested.

2. Which access arrangement is sufficient for the server folder?

A database directory grant alone. Client filesystem access alone. The required database directory grants plus server OS access to an existing folder. A matching folder name on the laptop.

3. What does EXIT_CLIENT do for a running job?

It deletes the dump. It kills the job. It stops the job for later restart. It detaches the client while the job continues.

4. A row shows EXECUTING and ATTACHED_SESSIONS=0. What does it mean?

The server job is running without attached clients. The export has failed. No server process can be working. The dump is complete.

5. Which result establishes readiness to use the export?

The dump filename exists. The job row disappeared. The final completion result, reviewed errors and skipped objects, and reconciled scope and counts. Closing the terminal.

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