EXPDB BASIC

Oracle EXPDP (Data Pump Export) provides a powerful set of parameters for exporting Oracle database objects into a dump file, which can be later imported using IMPDP. Here’s a comprehensive list of the most useful EXPDP parameters, grouped by their purposes:

1. General Parameters

a. DIRECTORY

• Specifies the directory object where the dump files and log files will be written.

Example:
bash
Copy code
DIRECTORY=datapump_dir

b. DUMPFILE

• Specifies the name of the dump file where the exported data will be stored. You can also specify multiple dump files using wildcards (%U).

Example:
bash
Copy code
DUMPFILE=full_exp_%U.dmp

c. LOGFILE

• Specifies the name of the log file that will store the output messages from the export operation.

Example:
bash
Copy code
LOGFILE=expdp_full.log

d. JOB_NAME

• Defines a name for the Data Pump job, useful when you want to manage or monitor the job later.

Example:
bash
Copy code
JOB_NAME=my_expdp_job

e. VERSION

• Specifies the Oracle database version of the objects to be exported. This allows you to export data that is compatible with earlier Oracle versions.

Example:
bash
Copy code
VERSION=12.2

2. Object-Level Filters

a. SCHEMAS

• Specifies which schemas to export. By default, Data Pump exports the current schema.

Example:
bash
Copy code
SCHEMAS=hr,scott

b. TABLES

• Specifies the list of tables to export. You can also specify partitions.

Example:
bash
Copy code
TABLES=hr.employees,hr.departments

c. TABLESPACES

• Exports all objects within the specified tablespaces.

Example:
bash
Copy code
TABLESPACES=users,example

d. FULL

• Specifies that the entire database will be exported.

Example:
bash
Copy code
FULL=Y

e. INCLUDE and EXCLUDE

• INCLUDE specifies what type of objects should be included.

EXCLUDE specifies what type of objects should be excluded.
Examples:
bash
Copy code
INCLUDE=TABLE:"IN ('EMPLOYEES', 'DEPARTMENTS')"

EXCLUDE=INDEX:"LIKE 'EMP%'"

f. QUERY

• Exports a subset of rows from a table based on a query.

Example:
bash
Copy code
QUERY=employees:"WHERE department_id=10"

3. Performance-Related Parameters

a. PARALLEL

• Specifies the number of processes (worker threads) to use for the export. Increasing this can improve performance on large exports.

Example:
bash
Copy code
PARALLEL=4

b. COMPRESSION

• Controls the level of compression applied to the dump file. ALL compresses both metadata and data, reducing file size.

Example:
bash
Copy code
COMPRESSION=ALL

c. ESTIMATE_ONLY

• Generates an estimate of how large the export will be without actually performing the export.

Example:
bash
Copy code
ESTIMATE_ONLY=Y

d. ESTIMATE

• Provides an estimate for the size of the export operation (BLOCKS or STATISTICS).

Example:
bash
Copy code
ESTIMATE=BLOCKS

e. ACCESS_METHOD

• Specifies the method Data Pump uses to extract data. It can be DIRECT_PATH or EXTERNAL_TABLE.

Example:
bash
Copy code
ACCESS_METHOD=DIRECT_PATH

4. Data Handling Parameters

a. ENCRYPTION

• Specifies the encryption method for the dump file. Options include ENCRYPTED_COLUMNS_ONLY, DATA_ONLY, and ALL.

Example:
bash
Copy code
ENCRYPTION=ALL

b. ENCRYPTION_PASSWORD

• Provides the encryption password if the dump file is encrypted.

Example:
bash
Copy code
ENCRYPTION_PASSWORD=my_password

c. DATA_OPTIONS

• Controls various options related to data export. For example, SKIP_CONSTRAINT_ERRORS skips rows that cause constraint errors.

Example:
bash
Copy code
DATA_OPTIONS=SKIP_CONSTRAINT_ERRORS

d. REUSE_DUMPFILES

• Overwrites existing dump files if they already exist.

Example:
bash
Copy code
REUSE_DUMPFILES=Y

e. FLASHBACK_TIME or FLASHBACK_SCN

Ensures the export represents the database at a consistent point in time using Oracle’s Flashback technology.
Examples:
bash
Copy code
FLASHBACK_TIME="TO_TIMESTAMP('2024-09-12 12:00:00', 'YYYY-MM-DD HH24:MI:SS')"

FLASHBACK_SCN=123456789

5. Job Management and Control

a. ATTACH

• Attaches to an existing Data Pump job for monitoring or control purposes.

Example:
bash
Copy code
ATTACH=my_expdp_job

b. STATUS

• Displays the status of the Data Pump job at regular intervals.

Example:
bash
Copy code
STATUS=60

c. STOP_JOB and START_JOB

• Stops or starts the export job.

Example:
bash
Copy code
STOP_JOB=IMMEDIATE

START_JOB=Y

d. KEEP_MASTER

• Keeps the master table after the job completes. The master table contains metadata about the job and can be useful for troubleshooting.

Example:
bash
Copy code
KEEP_MASTER=Y

6. Miscellaneous Parameters

a. METRICS

• Displays detailed performance metrics for the export job.

Example:
bash
Copy code
METRICS=

b. CONTENT

• Specifies whether to export metadata (METADATA_ONLY), data (DATA_ONLY), or both (ALL).

Example:
bash
Copy code
CONTENT=ALL

c. REMAP_TABLESPACE

• Changes the target tablespace for objects during export. Useful for reorganizing tablespaces during a database migration.

Example:
bash
Copy code
REMAP_TABLESPACE=users:example

d. REMAP_SCHEMA

• Changes the schema name during export, helpful for migrating a schema to a different user.

Example:
bash
Copy code
REMAP_SCHEMA=scott:hr

7. Monitoring and Logging Parameters

a. NOLOGFILE

• Disables the generation of the log file.

Example:
bash
Copy code
NOLOGFILE=Y

b. TRACE

• Enables detailed tracing for troubleshooting, specifying different levels of trace details.

Example:
bash
Copy code
TRACE=480300

8. Example Commands Using Multiple Parameters

a. Full Database Export with Parallelism and Compression

bash

Copy code

expdp system/password FULL=Y DIRECTORY=dpump_dir DUMPFILE=full_db_%U.dmp LOGFILE=expdp_full.log PARALLEL=4 COMPRESSION=ALL

b. Schema Export with Flashback and Encryption

bash

Copy code

expdp system/password SCHEMAS=hr DIRECTORY=dpump_dir DUMPFILE=hr_exp.dmp LOGFILE=hr_exp.log ENCRYPTION=ALL ENCRYPTION_PASSWORD=my_password FLASHBACK_TIME="TO_TIMESTAMP('2024-09-12 12:00:00', 'YYYY-MM-DD HH24:MI:SS')"

c. Export Only Data from Specific Tables

bash

Copy code

expdp system/password TABLES=hr.employees,hr.departments DIRECTORY=dpump_dir DUMPFILE=tables_exp.dmp LOGFILE=tables_exp.log CONTENT=DATA_ONLY

These EXPDP parameters provide Oracle DBAs with great flexibility in controlling the export process, whether it's for full database backups, schema migrations, or selective data extraction. Proper use of these options ensures efficient and optimized backups that meet specific requirements.

IMPDP Basic

IMPDP (Data Pump Import) is the utility used in Oracle to import data and database objects from dump files created by EXPDP (Data Pump Export). It is a more flexible and high-performance tool than the traditional IMP utility. Here’s a basic overview of IMPDP and its essential concepts and parameters.

1. Basic Syntax of IMPDP

The basic syntax for running the IMPDP command is:

bash

Copy code

impdp <username>/<password> DIRECTORY=<directory_object> DUMPFILE=<dumpfile_name> [options]

• <username>: The Oracle username with necessary privileges to import.

• <password>: The password for the Oracle user.

• DIRECTORY: Specifies the directory object where the dump file is located.

• DUMPFILE: Specifies the name of the dump file to be imported.

2. Basic Parameters

a. DIRECTORY

• Specifies the Oracle directory object where the dump file(s) are located. This directory should be accessible and pre-created by the DBA.

Example:
bash
Copy code
DIRECTORY=datapump_dir

b. DUMPFILE

• Specifies the dump file(s) generated by EXPDP to be imported.

Example:
bash
Copy code
DUMPFILE=full_db_exp.dmp

c. LOGFILE

• Specifies the name of the log file where the import process's output and messages will be recorded.

Example:
bash
Copy code
LOGFILE=impdp_full.log

d. SCHEMAS

• Specifies which schemas to import from the dump file. This is helpful when you want to import only specific schemas.

Example:
bash
Copy code
SCHEMAS=hr,scott

e. TABLES

• Specifies a list of tables to import. You can choose specific tables from a schema to import.

Example:
bash
Copy code
TABLES=hr.employees,hr.departments

f. FULL

• Specifies that the entire database should be imported. This is typically used when performing a full database restore.

Example:
bash
Copy code
FULL=Y

3. Commonly Used Parameters for IMPDP

a. REMAP_SCHEMA

• Changes the schema into which objects are imported. This is useful when you want to import a schema from one user to another.

Example:
bash
Copy code
REMAP_SCHEMA=scott:hr

b. REMAP_TABLESPACE

• Changes the tablespace into which objects are imported. This is useful if you need to map objects to different tablespaces in the target database.

Example:
bash
Copy code
REMAP_TABLESPACE=users:example

c. CONTENT

• Specifies whether to import all data (ALL), only metadata (METADATA_ONLY), or only table data (DATA_ONLY).

Example:
bash
Copy code
CONTENT=DATA_ONLY

d. EXCLUDE and INCLUDE

• EXCLUDE: Specifies the type of objects to exclude from the import process.

INCLUDE: Specifies the type of objects to include in the import process.
Example for excluding indexes:
bash
Copy code
EXCLUDE=INDEX

e. PARALLEL

• Specifies the number of worker processes to be used for the import operation. Increasing the number of processes can improve performance.

Example:
bash
Copy code
PARALLEL=4

f. TABLE_EXISTS_ACTION

• Specifies the action to take when importing a table that already exists in the target database. Options are:

  • SKIP: Do not overwrite existing table data.

  • APPEND: Append rows to the existing table.

  • REPLACE: Drop and recreate the table, then import the data.

  • TRUNCATE: Truncate the table, then import the data.

Example:
bash
Copy code
TABLE_EXISTS_ACTION=REPLACE

g. TRANSFORM

• Allows for transformation of certain attributes during the import. For example, you can disable constraints or modify storage parameters.

Example to disable segment creation:
bash
Copy code
TRANSFORM=SEGMENT_ATTRIBUTES:N

4. Monitoring and Managing the Import Job

a. STATUS

• Displays the status of the import job at regular intervals (in seconds).

Example:
bash
Copy code
STATUS=60

b. JOB_NAME

• Specifies a name for the Data Pump job. This is useful when you want to track or manage the job later.

Example:
bash
Copy code
JOB_NAME=my_import_job

c. ATTACH

• Attaches to a running import job so you can monitor or control it (e.g., stop or restart).

Example:
bash
Copy code
ATTACH=my_import_job

5. Basic Example Commands

a. Schema Import

Importing the hr schema from the full_db_exp.dmp dump file:

bash

Copy code

impdp system/password DIRECTORY=datapump_dir DUMPFILE=full_db_exp.dmp SCHEMAS=hr LOGFILE=impdp_hr.log

b. Full Database Import

Importing the entire database from the dump file:

bash

Copy code

impdp system/password DIRECTORY=datapump_dir DUMPFILE=full_db_exp.dmp FULL=Y LOGFILE=impdp_full.log

c. Table-Level Import

Importing specific tables from the dump file:

bash

Copy code

impdp system/password DIRECTORY=datapump_dir DUMPFILE=full_db_exp.dmp TABLES=hr.employees,hr.departments LOGFILE=impdp_tables.log

d. Importing with Schema and Tablespace Remapping

Importing the scott schema into the hr schema and remapping the users tablespace to example:

bash

Copy code

impdp system/password DIRECTORY=datapump_dir DUMPFILE=full_db_exp.dmp REMAP_SCHEMA=scott:hr REMAP_TABLESPACE=users:example LOGFILE=impdp_remap.log

Conclusion

IMPDP is a highly flexible and efficient tool for importing Oracle database objects from dump files created by EXPDP. By understanding and using the appropriate parameters, you can control how data is imported, manage resources effectively, and optimize performance.