Infrastructure at your Service

Alain Fuhrer

DataPump and the Transform option – Not very well known, but it can be useful

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 the data.
We 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.

Everyone working with oracle and datapump knows, that there are multiple options available to transform the data during the import. Well known are:

  • REMAP_DATAFILE
  • REMAPE_TABLESPACE
  • REMAP_SCHEMA

But there is an additional option available to transforming the metadata and that’s 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 here

SEGMENT_ATTRIBUTES:[Y | N]:[table | index ]

The default value for the segment_attributes during import is transform=segment_attributes:y. This means, that the object will be imported with the same segment attributes like in the source database. If you set transform=segment_attributes:n 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.

LOB_STORAGE:[SECUREFILE | BASICFILE | DEFAULT | NO_CHANGE]

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.

For example if you upgrade a database with export / import from 12.1 to 19c and you have LOB’s in your database, then per default the import will create also BASICFILE LOB’s 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’s and BASICFILE LOB’s. To avoid that you can use the LOB_STORAGE option of the TRANSFORM clause in datapump.

TRANSFORM=LOB_STORAGE:SECUREFILE

With this import parameter oracle will create all LOB Segments as SECUREFILES also if they are exported as BASICFILE.

OID:[Y | N]

The last option that could really help you is TRANSFORM=OID:n. I come back to my full transportable migration that I did for a customer. During the metadata import we get the following errors:

ORA-39082: Object type TYPE_BODY:"XXX"."XXX" created with compilation warnings

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 TRANSFORM=OID:n so oracle will create new OID during the import and will not use the OID of the source database.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Alain Fuhrer
Alain Fuhrer

Delivery Manager & Consultant