{"id":16849,"date":"2021-11-17T10:05:02","date_gmt":"2021-11-17T09:05:02","guid":{"rendered":"https:\/\/www.dbi-services.com\/blog\/datapump-and-the-transform-option-not-very-well-known-but-it-can-be-useful\/"},"modified":"2023-01-06T11:36:24","modified_gmt":"2023-01-06T10:36:24","slug":"datapump-and-the-transform-option-not-very-well-known-but-it-can-be-useful","status":"publish","type":"post","link":"https:\/\/www.dbi-services.com\/blog\/datapump-and-the-transform-option-not-very-well-known-but-it-can-be-useful\/","title":{"rendered":"DataPump and the Transform option &#8211; Not very well known, but it can be useful"},"content":{"rendered":"<p>I work with Oracle Databases and Datapump for more than 10 years but still I find some datapump options that I did know and in some cases can be very helpful.<br \/>\nFor a customer we did a migration of a 2TB database from AIX to Linux which means we had to change the endianness of the data.<br \/>\nWe decided to perform the Migration with Full Transportable database, convert the database files with rman and afterwards import the metadata with datapump. The migration procedure is not part of this blog, but it is the reason why I discover the transform option of datapump.<\/p>\n<p>Everyone working with oracle and datapump knows, that there are multiple options available to transform the data during the import. Well known are:<\/p>\n<ul>\n<li>REMAP_DATAFILE<\/li>\n<li>REMAPE_TABLESPACE<\/li>\n<li>REMAP_SCHEMA<\/li>\n<\/ul>\n<p>But there is an additional option available to transforming the metadata and that\u2019s the not very well known TRANSFORM option of datapump. The transform option supports multiple options to transform objects during the import. I will show a little bit more about 3 options, but the full list can be found <a href=\"https:\/\/docs.oracle.com\/en\/database\/oracle\/oracle-database\/19\/sutil\/datapump-import-utility.html#GUID-64FB67BD-EB67-4F50-A4D2-5D34518E6BDB\">here<\/a><\/p>\n<p><strong>SEGMENT_ATTRIBUTES:[Y | N]:[table | index ]<\/strong><\/p>\n<p>The default value for the segment_attributes during import is <em>transform=segment_attributes:y<\/em>. This means, that the object will be imported with the same segment attributes like in the source database. If you set <em>transform=segment_attributes:n<\/em> then datapump will ignore the attributes in the dumpfile and will use the tablespace\/user default values. For example if table t1 of user TEST_USER was stored on tablespace USER_DATA in the source database and the default tablespace of TEST_USER is USERS, then the segment will be created there.<\/p>\n<p><strong>LOB_STORAGE:[SECUREFILE | BASICFILE | DEFAULT | NO_CHANGE]<\/strong><\/p>\n<p>This option could be very interesting for migrations from older oracle releases to newer ones. Until Oracle 12.1 the default storage option for LOBs was BASICFILE. Since 12.2 the new default is SECUREFILE. The BASICFILE option is still possible but deprecated. So this will be desupported in future releases and in that case you should change this during a upgrade.<\/p>\n<p>For example if you upgrade a database with export \/ import from 12.1 to 19c and you have LOB\u2019s in your database, then per default the import will create also BASICFILE LOB\u2019s in the 19c database. After the migration the default for LOB creation is SECUREFILE and in this case you will get a mix of SECUREFILE LOB\u2019s and BASICFILE LOB\u2019s. To avoid that you can use the LOB_STORAGE option of the TRANSFORM clause in datapump.<\/p>\n<p><em>TRANSFORM=LOB_STORAGE:SECUREFILE<\/em><\/p>\n<p>With this import parameter oracle will create all LOB Segments as SECUREFILES also if they are exported as BASICFILE.<\/p>\n<p><strong>OID:[Y | N]<\/strong><\/p>\n<p>The last option that could really help you is <em>TRANSFORM=OID:n.<\/em> I come back to my full transportable migration that I did for a customer. During the metadata import we get the following errors:<\/p>\n<pre class=\"brush: sql; gutter: false; first-line: 1\">ORA-39082: Object type TYPE_BODY:\"XXX\".\"XXX\" created with compilation warnings<\/pre>\n<p>The application in this case use types in the code. Every type in the oracle database is identified with a unique identifier (OID) and the problem was, that in the target database we had already OID with the same value. In this case we used the parameter <em>TRANSFORM=OID:n<\/em> so oracle will create new OID during the import and will not use the OID of the source database.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>I work with Oracle Databases and Datapump for more than 10 years but still I find some datapump options that I did know and in some cases can be very helpful. For a customer we did a migration of a 2TB database from AIX to Linux which means we had to change the endianness of [&hellip;]<\/p>\n","protected":false},"author":27,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[229],"tags":[734,96],"type_dbi":[],"class_list":["post-16849","post","type-post","status-publish","format-standard","hentry","category-database-administration-monitoring","tag-datapump","tag-oracle"],"acf":[],"yoast_head":"<!-- This site is optimized with the Yoast SEO Premium plugin v27.2 (Yoast SEO v27.2) - https:\/\/yoast.com\/product\/yoast-seo-premium-wordpress\/ -->\n<title>DataPump and the Transform option - Not very well known, but it can be useful - dbi Blog<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.dbi-services.com\/blog\/datapump-and-the-transform-option-not-very-well-known-but-it-can-be-useful\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"DataPump and the Transform option - Not very well known, but it can be useful\" \/>\n<meta property=\"og:description\" content=\"I work with Oracle Databases and Datapump for more than 10 years but still I find some datapump options that I did know and in some cases can be very helpful. For a customer we did a migration of a 2TB database from AIX to Linux which means we had to change the endianness of [&hellip;]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.dbi-services.com\/blog\/datapump-and-the-transform-option-not-very-well-known-but-it-can-be-useful\/\" \/>\n<meta property=\"og:site_name\" content=\"dbi Blog\" \/>\n<meta property=\"article:published_time\" content=\"2021-11-17T09:05:02+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2023-01-06T10:36:24+00:00\" \/>\n<meta name=\"author\" content=\"Oracle Team\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Oracle Team\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"3 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/datapump-and-the-transform-option-not-very-well-known-but-it-can-be-useful\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/datapump-and-the-transform-option-not-very-well-known-but-it-can-be-useful\/\"},\"author\":{\"name\":\"Oracle Team\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee\"},\"headline\":\"DataPump and the Transform option &#8211; Not very well known, but it can be useful\",\"datePublished\":\"2021-11-17T09:05:02+00:00\",\"dateModified\":\"2023-01-06T10:36:24+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/datapump-and-the-transform-option-not-very-well-known-but-it-can-be-useful\/\"},\"wordCount\":568,\"commentCount\":0,\"keywords\":[\"DataPump\",\"Oracle\"],\"articleSection\":[\"Database Administration &amp; Monitoring\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/datapump-and-the-transform-option-not-very-well-known-but-it-can-be-useful\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/datapump-and-the-transform-option-not-very-well-known-but-it-can-be-useful\/\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/datapump-and-the-transform-option-not-very-well-known-but-it-can-be-useful\/\",\"name\":\"DataPump and the Transform option - Not very well known, but it can be useful - dbi Blog\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#website\"},\"datePublished\":\"2021-11-17T09:05:02+00:00\",\"dateModified\":\"2023-01-06T10:36:24+00:00\",\"author\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/datapump-and-the-transform-option-not-very-well-known-but-it-can-be-useful\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/datapump-and-the-transform-option-not-very-well-known-but-it-can-be-useful\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/datapump-and-the-transform-option-not-very-well-known-but-it-can-be-useful\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Accueil\",\"item\":\"https:\/\/www.dbi-services.com\/blog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"DataPump and the Transform option &#8211; Not very well known, but it can be useful\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#website\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/\",\"name\":\"dbi Blog\",\"description\":\"\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.dbi-services.com\/blog\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee\",\"name\":\"Oracle Team\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/secure.gravatar.com\/avatar\/f711f7cd2c9b09bf2627133755b569fb5be0694810cfd33033bdd095fedba86d?s=96&d=mm&r=g\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/f711f7cd2c9b09bf2627133755b569fb5be0694810cfd33033bdd095fedba86d?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/f711f7cd2c9b09bf2627133755b569fb5be0694810cfd33033bdd095fedba86d?s=96&d=mm&r=g\",\"caption\":\"Oracle Team\"},\"url\":\"https:\/\/www.dbi-services.com\/blog\/author\/oracle-team\/\"}]}<\/script>\n<!-- \/ Yoast SEO Premium plugin. -->","yoast_head_json":{"title":"DataPump and the Transform option - Not very well known, but it can be useful - dbi Blog","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.dbi-services.com\/blog\/datapump-and-the-transform-option-not-very-well-known-but-it-can-be-useful\/","og_locale":"en_US","og_type":"article","og_title":"DataPump and the Transform option - Not very well known, but it can be useful","og_description":"I work with Oracle Databases and Datapump for more than 10 years but still I find some datapump options that I did know and in some cases can be very helpful. For a customer we did a migration of a 2TB database from AIX to Linux which means we had to change the endianness of [&hellip;]","og_url":"https:\/\/www.dbi-services.com\/blog\/datapump-and-the-transform-option-not-very-well-known-but-it-can-be-useful\/","og_site_name":"dbi Blog","article_published_time":"2021-11-17T09:05:02+00:00","article_modified_time":"2023-01-06T10:36:24+00:00","author":"Oracle Team","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Oracle Team","Est. reading time":"3 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.dbi-services.com\/blog\/datapump-and-the-transform-option-not-very-well-known-but-it-can-be-useful\/#article","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/datapump-and-the-transform-option-not-very-well-known-but-it-can-be-useful\/"},"author":{"name":"Oracle Team","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee"},"headline":"DataPump and the Transform option &#8211; Not very well known, but it can be useful","datePublished":"2021-11-17T09:05:02+00:00","dateModified":"2023-01-06T10:36:24+00:00","mainEntityOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/datapump-and-the-transform-option-not-very-well-known-but-it-can-be-useful\/"},"wordCount":568,"commentCount":0,"keywords":["DataPump","Oracle"],"articleSection":["Database Administration &amp; Monitoring"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.dbi-services.com\/blog\/datapump-and-the-transform-option-not-very-well-known-but-it-can-be-useful\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.dbi-services.com\/blog\/datapump-and-the-transform-option-not-very-well-known-but-it-can-be-useful\/","url":"https:\/\/www.dbi-services.com\/blog\/datapump-and-the-transform-option-not-very-well-known-but-it-can-be-useful\/","name":"DataPump and the Transform option - Not very well known, but it can be useful - dbi Blog","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/#website"},"datePublished":"2021-11-17T09:05:02+00:00","dateModified":"2023-01-06T10:36:24+00:00","author":{"@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee"},"breadcrumb":{"@id":"https:\/\/www.dbi-services.com\/blog\/datapump-and-the-transform-option-not-very-well-known-but-it-can-be-useful\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.dbi-services.com\/blog\/datapump-and-the-transform-option-not-very-well-known-but-it-can-be-useful\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.dbi-services.com\/blog\/datapump-and-the-transform-option-not-very-well-known-but-it-can-be-useful\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Accueil","item":"https:\/\/www.dbi-services.com\/blog\/"},{"@type":"ListItem","position":2,"name":"DataPump and the Transform option &#8211; Not very well known, but it can be useful"}]},{"@type":"WebSite","@id":"https:\/\/www.dbi-services.com\/blog\/#website","url":"https:\/\/www.dbi-services.com\/blog\/","name":"dbi Blog","description":"","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.dbi-services.com\/blog\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee","name":"Oracle Team","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/f711f7cd2c9b09bf2627133755b569fb5be0694810cfd33033bdd095fedba86d?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/f711f7cd2c9b09bf2627133755b569fb5be0694810cfd33033bdd095fedba86d?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/f711f7cd2c9b09bf2627133755b569fb5be0694810cfd33033bdd095fedba86d?s=96&d=mm&r=g","caption":"Oracle Team"},"url":"https:\/\/www.dbi-services.com\/blog\/author\/oracle-team\/"}]}},"_links":{"self":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/16849","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/users\/27"}],"replies":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/comments?post=16849"}],"version-history":[{"count":1,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/16849\/revisions"}],"predecessor-version":[{"id":21463,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/16849\/revisions\/21463"}],"wp:attachment":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/media?parent=16849"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/categories?post=16849"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/tags?post=16849"},{"taxonomy":"type","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/type_dbi?post=16849"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}