{"id":88,"date":"2024-12-14T15:06:05","date_gmt":"2024-12-14T15:06:05","guid":{"rendered":"https:\/\/w3buddy.com\/?p=88"},"modified":"2026-01-15T13:11:25","modified_gmt":"2026-01-15T07:41:25","slug":"oracle-data-pump-export-expdp-a-complete-guide","status":"publish","type":"post","link":"https:\/\/w3buddy.com\/blog\/oracle-data-pump-export-expdp-a-complete-guide\/","title":{"rendered":"Oracle Data Pump Export (EXPDP): A Complete Guide"},"content":{"rendered":"\n<p class=\"wp-block-paragraph\">Oracle EXPDP is part of the Oracle Data Pump suite, allowing you to export database objects, data, and metadata into dump files. These dump files can then be imported into another database using Oracle Data Pump Import (IMPDP).<\/p>\n\n\n\n<h3 class=\"wp-block-heading\"><strong>Key Features of EXPDP<\/strong><\/h3>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong>Parallel Processing:<\/strong> Speeds up large exports by using multiple threads.<\/li>\n\n\n\n<li><strong>Compression:<\/strong> Reduces dump file sizes by compressing data and\/or metadata.<\/li>\n\n\n\n<li><strong>Fine-Grained Control:<\/strong> Allows selective export using parameters like <code>SCHEMAS<\/code>, <code>TABLES<\/code>, <code>QUERY<\/code>, and more.<\/li>\n\n\n\n<li><strong>Transportable Tablespaces:<\/strong> Facilitates fast migration of large datasets between databases.<\/li>\n\n\n\n<li><strong>Flashback Technology:<\/strong> Ensures consistency of the exported data by using Oracle&#8217;s flashback features.<\/li>\n<\/ul>\n\n\n\n<h2 class=\"wp-block-heading\"><strong>EXPDP Command Syntax<\/strong><\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">The basic syntax for running an EXPDP command is:<\/p>\n\n\n\n<pre class=\"wp-block-prismatic-blocks\"><code class=\"language-sql\">nohup expdp '\"\/ as sysdba\"' DIRECTORY=directory_name \n  DUMPFILE=dumpfile_name.dmp [,dumpfile_name2.dmp, dumpfile_name3.dmp, ...] \n  LOGFILE=logfile_name.log \n  FULL=Y|N \n  SCHEMAS=schema_name [,schema_name] \n  TABLES=table_name [,table_name] or owner.tablename\n  INCLUDE=object_type [,object_type] \n  EXCLUDE=object_type [,object_type] \n  CONTENT=ALL|DATA_ONLY|METADATA_ONLY \n  FLASHBACK_SCN=scn_number \n  FLASHBACK_TIME=\"timestamp\" \n  NETWORK_LINK=network_link_name \n  TRANSPORT_TABLESPACES=tablespace_name [,tablespace_name] \n  TRANSPORTABLE=ALWAYS|AUTOMATIC|NEVER \n  COMPRESSION=ALL|DATA|METADATA|NONE \n  ESTIMATE_ONLY=Y|N \n  PARALLEL=number_of_threads \n  QUERY=table_name:\"WHERE condition\" \n  REUSE_DUMPFILES=Y|N \n  ROWS=Y|N \n  TABLESPACES=tablespace_name [,tablespace_name] \n  VERSION=version_number \n  JOB_NAME=job_name \n  ENCRYPTION=ALL|DATA|METADATA|NONE \n  ENCRYPTION_ALGORITHM=algorithm_name \n  ENCRYPTION_PASSWORD=password \n  ENCRYPTION_TRANSFORM=transform_name \n  LOGTIME=ALL|NONE|DATE \n  DATA_OPTIONS=option [,option] \n  TRANSFORM=transform_option [,transform_option] \n  DISABLE_ARCHIVE_LOGGING=Y|N\n  RAC=Y|N &amp;<\/code><\/pre>\n\n\n\n<h2 class=\"wp-block-heading\"><strong>Example: EXPDP Command<\/strong><\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">Here\u2019s a practical example to demonstrate the usage of multiple parameters:<\/p>\n\n\n\n<pre class=\"wp-block-prismatic-blocks\"><code class=\"language-sql\">nohup expdp '\"\/ as sysdba\"' DIRECTORY=my_dump_dir \n  DUMPFILE=my_dumpfile_%U.dmp,my_dumpfile_%U_%I.dmp \n  LOGFILE=my_export.log \n  FULL=N \n  SCHEMAS=hr,sales \n  TABLES=employees,departments or owner1.tablename, owner2.tablename\n  INCLUDE=TABLE:\"IN ('EMPLOYEES', 'DEPARTMENTS')\" \n  EXCLUDE=TABLE:\"IN ('TEMP_TABLES')\" \n  CONTENT=ALL \n  FLASHBACK_SCN=123456789 \n  FLASHBACK_TIME=\"2024-08-20 12:00:00\" \n  NETWORK_LINK=my_remote_db_link \n  TRANSPORT_TABLESPACES=users,system \n  TRANSPORTABLE=AUTOMATIC \n  COMPRESSION=ALL \n  ESTIMATE_ONLY=N \n  PARALLEL=4 \n  QUERY=employees:\"WHERE department_id=10\" \n  REUSE_DUMPFILES=N \n  ROWS=Y \n  TABLESPACES=users,system \n  VERSION=19 \n  JOB_NAME=my_export_job \n  ENCRYPTION=DATA \n  ENCRYPTION_ALGORITHM=AES256 \n  ENCRYPTION_PASSWORD=my_password \n  ENCRYPTION_TRANSFORM=PASSWORD \n  LOGTIME=DATE \n  DATA_OPTIONS=ROWS \n  TRANSFORM=DISABLE_ARCHIVE_LOGGING:Y,CONSTRAINTS:N\n  DISABLE_ARCHIVE_LOGGING=Y\n  RAC=Y &amp;<\/code><\/pre>\n\n\n\n<h2 class=\"wp-block-heading\"><strong>EXPDP Parameters Explained<\/strong><\/h2>\n\n\n\n<h4 class=\"wp-block-heading\"><strong>1. DIRECTORY<\/strong><\/h4>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Specifies the directory object where dump files and log files are written.<\/li>\n\n\n\n<li><strong>Example:<\/strong> <code>DIRECTORY=my_dump_dir<\/code><\/li>\n<\/ul>\n\n\n\n<h4 class=\"wp-block-heading\"><strong>2. DUMPFILE<\/strong><\/h4>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Defines the names of the dump files to be created. Supports wildcard suffixes for multiple files.<\/li>\n\n\n\n<li><strong>Example:<\/strong> <code>DUMPFILE=my_dumpfile_%U.dmp<\/code><\/li>\n<\/ul>\n\n\n\n<h4 class=\"wp-block-heading\"><strong>3. LOGFILE<\/strong><\/h4>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Specifies the log file to record the export operation.<\/li>\n\n\n\n<li><strong>Example:<\/strong> <code>LOGFILE=my_export.log<\/code><\/li>\n<\/ul>\n\n\n\n<h4 class=\"wp-block-heading\"><strong>4. FULL<\/strong><\/h4>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Exports the entire database when set to Y. Default is N.<\/li>\n\n\n\n<li><strong>Example:<\/strong> <code>FULL=Y<\/code><\/li>\n<\/ul>\n\n\n\n<h4 class=\"wp-block-heading\"><strong>5. SCHEMAS<\/strong><\/h4>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Exports specified schemas.<\/li>\n\n\n\n<li><strong>Example:<\/strong> <code>SCHEMAS=hr,sales<\/code><\/li>\n<\/ul>\n\n\n\n<h4 class=\"wp-block-heading\"><strong>6. TABLES<\/strong><\/h4>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Exports specific tables.<\/li>\n\n\n\n<li><strong>Example:<\/strong> <code>TABLES=employees,departments<\/code><\/li>\n<\/ul>\n\n\n\n<h4 class=\"wp-block-heading\"><strong>7. INCLUDE\/EXCLUDE<\/strong><\/h4>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Includes or excludes specific object types during the export.<\/li>\n\n\n\n<li><strong>Example:<\/strong> <code>INCLUDE=TABLE:\"IN ('EMPLOYEES', 'DEPARTMENTS')\"<\/code><\/li>\n<\/ul>\n\n\n\n<h4 class=\"wp-block-heading\"><strong>8. CONTENT<\/strong><\/h4>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Specifies whether to export all data, only data, or only metadata.<\/li>\n\n\n\n<li><strong>Options:<\/strong> <code>ALL<\/code>, <code>DATA_ONLY<\/code>, <code>METADATA_ONLY<\/code><\/li>\n\n\n\n<li><strong>Example:<\/strong> <code>CONTENT=ALL<\/code><\/li>\n<\/ul>\n\n\n\n<h4 class=\"wp-block-heading\"><strong>9. FLASHBACK_SCN and FLASHBACK_TIME<\/strong><\/h4>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Ensures data consistency by using Oracle\u2019s Flashback technology.<\/li>\n\n\n\n<li><strong>SCN Example:<\/strong> <code>FLASHBACK_SCN=123456789<\/code><\/li>\n\n\n\n<li><strong>Timestamp Example:<\/strong> <code>FLASHBACK_TIME=\"2024-08-20 12:00:00\"<\/code><\/li>\n<\/ul>\n\n\n\n<h4 class=\"wp-block-heading\"><strong>10. PARALLEL<\/strong><\/h4>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Specifies the number of threads for parallel processing.<\/li>\n\n\n\n<li><strong>Example:<\/strong> <code>PARALLEL=4<\/code><\/li>\n<\/ul>\n\n\n\n<h4 class=\"wp-block-heading\"><strong>11. COMPRESSION<\/strong><\/h4>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Compresses data, metadata, or both to reduce dump file size.<\/li>\n\n\n\n<li><strong>Options:<\/strong> <code>ALL<\/code>, <code>DATA<\/code>, <code>METADATA<\/code>, <code>NONE<\/code><\/li>\n\n\n\n<li><strong>Example:<\/strong> <code>COMPRESSION=ALL<\/code><\/li>\n<\/ul>\n\n\n\n<h4 class=\"wp-block-heading\"><strong>12. ENCRYPTION and Related Parameters<\/strong><\/h4>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Encrypts the exported dump file.<\/li>\n\n\n\n<li><strong>Algorithm Example:<\/strong> <code>ENCRYPTION_ALGORITHM=AES256<\/code><\/li>\n\n\n\n<li><strong>Password Example:<\/strong> <code>ENCRYPTION_PASSWORD=my_password<\/code><\/li>\n<\/ul>\n\n\n\n<h4 class=\"wp-block-heading\"><strong>13. QUERY<\/strong><\/h4>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Filters data to be exported using a SQL WHERE clause.<\/li>\n\n\n\n<li><strong>Example:<\/strong> <code>QUERY=employees:\"WHERE department_id=10\"<\/code><\/li>\n<\/ul>\n\n\n\n<h4 class=\"wp-block-heading\"><strong>14. TRANSPORT_TABLESPACES<\/strong><\/h4>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Enables the export of transportable tablespaces.<\/li>\n\n\n\n<li><strong>Example:<\/strong> <code>TRANSPORT_TABLESPACES=users,system<\/code><\/li>\n<\/ul>\n\n\n\n<h4 class=\"wp-block-heading\"><strong>15. TRANSFORM<\/strong><\/h4>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Transforms data or metadata during the export.<\/li>\n\n\n\n<li><strong>Example:<\/strong> <code>TRANSFORM=DISABLE_ARCHIVE_LOGGING:Y<\/code><\/li>\n<\/ul>\n\n\n\n<h3 class=\"wp-block-heading\"><strong>Best Practices for Using EXPDP<\/strong><\/h3>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong>Use PARALLEL<\/strong> to speed up large exports.<\/li>\n\n\n\n<li><strong>Combine COMPRESSION and ENCRYPTION<\/strong> to secure and optimize dump files.<\/li>\n\n\n\n<li><strong>Review log files<\/strong> for warnings or errors after the export.<\/li>\n\n\n\n<li><strong>Test dump files&#8217; integrity<\/strong> before importing into a new database.<\/li>\n\n\n\n<li><strong>Regularly update directory objects<\/strong> and ensure permissions are set correctly.<\/li>\n<\/ul>\n\n\n\n<h3 class=\"wp-block-heading\"><strong>Conclusion<\/strong><\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">Oracle Data Pump Export (EXPDP) is an indispensable tool for database administrators. It provides flexibility and power for efficient data management. Mastering EXPDP will simplify your DBA tasks and enhance database reliability.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Stay tuned for our guide on <strong>Oracle Data Pump Import (IMPDP)<\/strong> to complement your learning.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Oracle EXPDP is part of the Oracle Data Pump suite, allowing you to export database objects, data, and metadata into dump files. These dump files can then be imported into another database using Oracle Data Pump Import (IMPDP). Key Features of EXPDP EXPDP Command Syntax The basic syntax for running an EXPDP command is: Example: [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"googlesitekit_rrm_CAowu461DA:productID":"","footnotes":""},"categories":[1225],"tags":[],"class_list":["post-88","post","type-post","status-publish","format-standard","hentry","category-database"],"_links":{"self":[{"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/posts\/88","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/comments?post=88"}],"version-history":[{"count":3,"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/posts\/88\/revisions"}],"predecessor-version":[{"id":1410,"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/posts\/88\/revisions\/1410"}],"wp:attachment":[{"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/media?parent=88"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/categories?post=88"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/tags?post=88"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}