{"id":2481,"date":"2025-01-25T13:45:12","date_gmt":"2025-01-25T13:45:12","guid":{"rendered":"https:\/\/w3buddy.com\/?p=2481"},"modified":"2026-01-15T13:19:07","modified_gmt":"2026-01-15T07:49:07","slug":"fixing-ora-39173-exporting-encrypted-data-without-encryption","status":"publish","type":"post","link":"https:\/\/w3buddy.com\/blog\/fixing-ora-39173-exporting-encrypted-data-without-encryption\/","title":{"rendered":"Fixing ORA-39173: Exporting Encrypted Data Without Encryption"},"content":{"rendered":"\n<p class=\"wp-block-paragraph\">When using Data Pump (expdp) to export data, you might encounter the warning:<\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><strong><mark style=\"background-color:#fffc00\" class=\"has-inline-color\">ORA-39173: Encrypted data has been stored unencrypted in dump file set.<\/mark><\/strong><\/p>\n\n\n\n<p class=\"wp-block-paragraph\">This typically happens when the exported data contains encrypted columns, but the dump file does not retain that encryption due to missing encryption options in the export command. Let\u2019s understand this better and resolve it.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\"><mark style=\"background-color:#fffc00\" class=\"has-inline-color\">Scenario<\/mark>: Exporting a Table with Encrypted Data<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">You attempt to export a table containing encrypted columns using the following command:<\/p>\n\n\n\n<pre class=\"wp-block-prismatic-blocks\"><code class=\"language-bash\">[oracle@test ~]$ expdp '\"\/ as sysdba\"' tables=hr.employee_data<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">The export runs, but you see the following warning:<\/p>\n\n\n\n<pre class=\"wp-block-prismatic-blocks\"><code class=\"language-bash\">. . exported \"HR\".\"EMPLOYEE_DATA\"  500 MB 250000 rows\nORA-39173: Encrypted data has been stored unencrypted in dump file set.\nMaster table \"SYS\".\"SYS_EXPORT_TABLE_01\" successfully loaded\/unloaded<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">This warning indicates that encrypted data in the table was exported without encryption in the dump file.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">Why Does This Happen?<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">By default, if you do not specify encryption options in the expdp command, encrypted columns in the source database are saved unencrypted in the dump file. This is intentional and serves as a warning, not an error.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">If encryption is required for compliance or security, you need to explicitly enable it in the export command.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\"><mark style=\"background-color:#31ff00\" class=\"has-inline-color\">Solution<\/mark>: Add Encryption Options to the Export Command<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">To ensure that encrypted columns remain encrypted in the dump file, use the encryption and encryption_mode parameters in the expdp command.<\/p>\n\n\n\n<h4 class=\"wp-block-heading\">Example Command with Encryption<\/h4>\n\n\n\n<pre class=\"wp-block-prismatic-blocks\"><code class=\"language-bash\">[oracle@test ~]$ expdp '\"\/ as sysdba\"' tables=hr.employee_data encryption=all encryption_mode=transparent<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Explanation:<\/strong><\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong>encryption=all<\/strong>: Ensures all data, including encrypted columns, is stored encrypted in the dump file.<\/li>\n\n\n\n<li><strong>encryption_mode=transparent<\/strong>: Suitable when restoring the dump file to the same database.<\/li>\n<\/ul>\n\n\n\n<p class=\"wp-block-paragraph\">If the dump file will be imported into a different database, use the <code>PASSWORD<\/code> mode instead:<\/p>\n\n\n\n<pre class=\"wp-block-prismatic-blocks\"><code class=\"language-bash\">[oracle@test ~]$ expdp '\"\/ as sysdba\"' tables=hr.employee_data encryption=all encryption_mode=password<\/code><\/pre>\n\n\n\n<h3 class=\"wp-block-heading\">Important Notes<\/h3>\n\n\n\n<ol start=\"1\" class=\"wp-block-list\">\n<li><strong>Wallet Requirements:<\/strong>\n<ul class=\"wp-block-list\">\n<li>Ensure the Oracle wallet is open before exporting. The wallet contains the encryption key required to process encrypted data.<\/li>\n\n\n\n<li>To check the wallet status, use:<\/li>\n<\/ul>\n<\/li>\n<\/ol>\n\n\n\n<pre class=\"wp-block-prismatic-blocks\"><code class=\"language-sql\">SQL> SELECT * FROM v$encryption_wallet;<\/code><\/pre>\n\n\n\n<ol start=\"1\" class=\"wp-block-list\">\n<li><strong>Scope Restrictions:<\/strong>\n<ul class=\"wp-block-list\">\n<li>The <strong>ENCRYPTED_COLUMNS_ONLY<\/strong> option is valid only with <strong>encryption_mode=password<\/strong>. It cannot be used with <strong>encryption_mode=transparent<\/strong>.<\/li>\n<\/ul>\n<\/li>\n<\/ol>\n\n\n\n<h3 class=\"wp-block-heading\">Conclusion<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">The ORA-39173 warning is not a critical error but a reminder to secure sensitive data during export. By adding the appropriate encryption options, you can ensure compliance with security requirements and protect your data effectively.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>When using Data Pump (expdp) to export data, you might encounter the warning: ORA-39173: Encrypted data has been stored unencrypted in dump file set. This typically happens when the exported data contains encrypted columns, but the dump file does not retain that encryption due to missing encryption options in the export command. Let\u2019s understand this [&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-2481","post","type-post","status-publish","format-standard","hentry","category-database"],"_links":{"self":[{"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/posts\/2481","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=2481"}],"version-history":[{"count":2,"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/posts\/2481\/revisions"}],"predecessor-version":[{"id":2483,"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/posts\/2481\/revisions\/2483"}],"wp:attachment":[{"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/media?parent=2481"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/categories?post=2481"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/tags?post=2481"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}