Got it, let's break down how to migrate your existing exp/imp selective export/import workflow to Oracle Data Pump—while fixing that slow performance issue and keeping your fully automated process intact. Here's a step-by-step guide tailored to your needs:

First, you'll need to map your existing query-based filtering directly to Data Pump's parameters. Unlike exp/imp, Data Pump uses directory objects to manage dump files, so start by setting that up (if you haven't already):

-- Create a directory object pointing to your shared server's dump file location
CREATE OR REPLACE DIRECTORY dump_shared AS '/path/to/your/shared/dump/folder';
-- Grant permissions to the user running Data Pump operations
GRANT READ, WRITE ON DIRECTORY dump_shared TO your_oracle_user;

Export with Query Filtering #

Use the QUERY parameter to replicate your selective data export. For multiple tables, specify a query per table:

expdp your_oracle_user/your_password@source_db \
  DIRECTORY=dump_shared \
  DUMPFILE=selective_data_%U.dmp \  # %U creates multiple files (great for parallelism)
  LOGFILE=exp_selective_$(date +%Y%m%d).log \
  TABLES=your_schema.table_a, your_schema.table_b \
  QUERY="your_schema.table_a:WHERE status = 'ACTIVE', your_schema.table_b:WHERE created_date >= SYSDATE - 30"

Import with Matching Logic #

For the target database, use the same query (if you need to re-filter) or skip it if you want all exported data. Use TABLE_EXISTS_ACTION to define how to handle existing data:

impdp your_oracle_user/your_password@target_db \
  DIRECTORY=dump_shared \
  DUMPFILE=selective_data_%U.dmp \
  LOGFILE=imp_selective_$(date +%Y%m%d).log \
  TABLES=your_schema.table_a, your_schema.table_b \
  TABLE_EXISTS_ACTION=TRUNCATE  # Options: REPLACE, APPEND, TRUNCATE, SKIP

Since your current workflow is slow, these Data Pump-specific optimizations will make a big difference:

• Parallel Processing: Add PARALLEL=4 (adjust based on your server's CPU cores) to split the work across multiple threads. Pair this with the %U wildcard in DUMPFILE to generate multiple dump files.
• Compression: Use COMPRESSION=DATA_ONLY to reduce dump file size (faster transfers and storage) without slowing down export/import too much. For maximum compression, use COMPRESSION=ALL.
• Minimize Logging: During import, add TRANSFORM=DISABLE_ARCHIVE_LOGGING:Y to turn off archive logging for the load (only do this if you don't need point-in-time recovery for the target tables during the import).
• Direct Path Mode: Data Pump uses this by default, but ensure you're not hitting limitations (like LOB columns with triggers) that force it to switch to slower external table mode.

Wrap the export/import commands in a shell script to match your existing automated process. Here's a sample:

#!/bin/bash

# Set variables for reusability
SOURCE_DB="source_db_tns"
TARGET_DB="target_db_tns"
USER="your_oracle_user"
PASS="your_password"
DIR="dump_shared"
DATE=$(date +%Y%m%d)

# Run export
echo "Starting export at $(date)"
expdp $USER/$PASS@$SOURCE_DB \
  DIRECTORY=$DIR \
  DUMPFILE=selective_exp_${DATE}_%U.dmp \
  LOGFILE=exp_${DATE}.log \
  TABLES=your_schema.table_a \
  QUERY="your_schema.table_a:WHERE updated_date >= SYSDATE - 7" \
  PARALLEL=4 \
  COMPRESSION=DATA_ONLY

# Check export success before importing
if [ $? -eq 0 ]; then
  echo "Export completed successfully. Starting import at $(date)"
  impdp $USER/$PASS@$TARGET_DB \
    DIRECTORY=$DIR \
    DUMPFILE=selective_exp_${DATE}_%U.dmp \
    LOGFILE=imp_${DATE}.log \
    TABLES=your_schema.table_a \
    TABLE_EXISTS_ACTION=APPEND \
    PARALLEL=4 \
    TRANSFORM=DISABLE_ARCHIVE_LOGGING:Y
else
  echo "Export failed. Aborting import."
  exit 1
fi

echo "Process completed at $(date)"

• Directory Permissions: Make sure the OS user running the script has read/write access to the physical folder, and the Oracle user has permissions on the directory object.
• Query Formatting: When using QUERY in a shell script, wrap the entire parameter in single quotes if your query contains spaces or special characters (to avoid shell interpretation issues).
• Version Compatibility: If your source and target databases are different versions, add the VERSION parameter (e.g., VERSION=12.2) to ensure compatibility.

内容的提问来源于stack exchange，提问作者Gary G