Master Table and Job Creation during export - expdp - Oracle

 

When you run the expdp (Data Pump Export) command, Oracle initiates a series of background processes and creates certain objects to perform the export operation. 

Here’s what happens step by step in simple terms:

1. Command Execution

• When you execute expdp, the client communicates with the Oracle database to begin the export process.
• Example:

  expdp system/password directory=EXPORT_DIR dumpfile=export.dmp logfile=export.log schemas=HR

---

2. Master Table and Job Creation

• Oracle creates a Master Table in the schema you are exporting from. This table tracks the progress of the export job, including metadata like table names, row counts, and dump file details.
  • Name of the Job and Table:
    • A job name like SYS_EXPORT_SCHEMA_01 is automatically generated or can be specified in the JOB_NAME parameter.
    • The Master Table is named the same as the job.
• The export process is identified by a job name and runs in the background using worker processes.

---

3. Directory Usage

• Oracle uses the directory object specified in the DIRECTORY parameter for:
  • Storing the dump files.
  • Storing the log file.
• The directory itself is an alias for a physical OS path, created using:

  CREATE OR REPLACE DIRECTORY EXPORT_DIR AS '/path/to/your/export/directory';

• The files in this directory will remain until you manually delete them.

---

4. Slave Processes in the Database

• When the export starts, the database spawns Data Pump Worker (DWn) and Master Process (DMn) as follows:
  • Master Process (DMn): Coordinates the entire export job.
  • Worker Processes (DWn): Perform the actual data unloading tasks, such as extracting table rows, writing dump files, etc.
• These are the key processes:
• DMnn: The Master process managing the job.
• DWnn: Worker processes handling data extraction and writing.

---

5. Temporary Files and Cleanup

• During the export, Oracle uses temporary files to manage intermediate data processing.
• Once the export finishes:
• The Master Table is deleted if the job completes successfully.
• The directory contents (dump files, log files) are not automatically deleted. You must clean them up manually.

---

6. What Happens After Completion

• The export job ends and leaves the dump files (.dmp) and log file (.log) in the directory you specified.
• If the job fails or is terminated, the Master Table may remain in the schema and must be manually dropped.

---

Example Walkthrough

1. You run:

   expdp user/password directory=EXPORT_DIR schemas=HR dumpfile=hr_schema.dmp logfile=hr_export.log

2. Step-by-step actions:
• Oracle creates a job called SYS_EXPORT_SCHEMA_01.
• A Master Table (SYS_EXPORT_SCHEMA_01) is created in the schema HR.
• The DMnn process starts.
• Worker processes (DWnn) extract rows from the HR schema and write them to hr_schema.dmp.
• After completion, the Master Table is dropped, and the dump and log files are left in /path/to/your/export/directory

---