Transportable Tablespaces is a great way of migrating data from one Oracle Database to another. However, if your database uses object types and those object types have evolved over time, you might hit an error during import:

W-1 Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE
ORA-39083: Object type TABLE:"APPUSER"."CARS" failed to create with error:
ORA-39218: type check on object type "APPUSER"."CAR_TYPE" failed
ORA-39216: object type "APPUSER"."CAR_TYPE" hashcode or version number mismatch

Failing sql is:
BEGIN SYS.DBMS_METADATA.CHECK_TYPE('APPUSER','CAR_TYPE','4','613350A0E37618A6108C500936D0C6162C',''); END;

Typically, there will be a number of dependent objects that fails with ORA-39112.

Let me explain the problem and what you can do about it.

Object Types and Evolution

The term object types refers to database objects that you create using the CREATE TYPE statement.

Object types are also known as user-defined types (UDT) and abstract data types (ADT).

When Oracle Database introduced object types many releases ago, they could not evolve. In simple words, you only had CREATE TYPE available. If you needed to change the type, you had to drop and recreate it. Later on, Oracle Database allowed type evolution, and you could also do ALTER TYPE.

An Example of Evolution

Let me illustrate object types and evolution using an example:

I create a supertype called VEHICLE_TYPE
I create another type called CAR_INFO_TYPE
I create a subtype called CAR_TYPE which inherits VEHICLE_TYPE and includes an attribute based on CAR_INFO_TYPE

I add the three types:

CREATE TYPE "APPUSER"."VEHICLE_TYPE" 
   IS OBJECT (
      make VARCHAR2 (40)
) NOT FINAL;
/
CREATE TYPE "APPUSER"."CAR_INFO_TYPE" 
   IS OBJECT (
      model VARCHAR2(40)
);
/
CREATE TYPE "APPUSER"."CAR_TYPE" 
   UNDER VEHICLE_TYPE (
      car_info CAR_INFO_TYPE
);
/

I query the data dictionary to find the types and verify that their version is 1. I expect this because the types are brand-new.

select o.name, t.version# 
from obj$ o, user$ u,type$ t 
where o.owner# = u.user# and o.oid$ = t.toid and u.name='APPUSER'
order by o.name, t.version#;

NAME             VERSION#
-------------------------
CAR_INFO_TYPE           1
CAR_TYPE                1
VEHICLE_TYPE            1

Now I want to add two additional attributes to CAR_INFO_TYPE:

horsepower
color

ALTER TYPE "APPUSER"."CAR_INFO_TYPE" ADD ATTRIBUTE horsepower NUMBER CASCADE
/
ALTER TYPE "APPUSER"."CAR_INFO_TYPE" ADD ATTRIBUTE color VARCHAR2(20) CASCADE
/

Now, I check the data dictionary again. CAR_INFO_TYPE has now advanced two versions to version 3. Two ALTER TYPE statements mean two new versions. But notice CAR_TYPE. It advances as well.

select o.name, t.version# 
from obj$ o, user$ u,type$ t 
where o.owner# = u.user# and o.oid$ = t.toid and u.name='APPUSER'
order by o.name, t.version#;

NAME             VERSION#
-------------------------
CAR_INFO_TYPE           1
CAR_INFO_TYPE           2
CAR_INFO_TYPE           3
CAR_TYPE                1
CAR_TYPE                2
CAR_TYPE                3
VEHICLE_TYPE            1

If you alter a type that another type uses, both types evolve. No ALTER TYPE was issued on CAR_TYPE, yet it still evolve. This becomes a problem later on.

Same Objects but Different Evolution

If I use the example above but change the order of statements, the types will evolve in a different way. They will still be the same objects, but they evolve differently.

Let me illustrate how the types can evolve differently. I execute the ALTER TYPE statements on CAR_INFO_TYPE immediately after its CREATE TYPE statement:

CREATE TYPE "APPUSER"."VEHICLE_TYPE" 
   IS OBJECT (
      make VARCHAR2 (40)
) NOT FINAL;
/
CREATE TYPE "APPUSER"."CAR_INFO_TYPE" 
   IS OBJECT (
      model VARCHAR2(40)
);
/
ALTER TYPE "APPUSER"."CAR_INFO_TYPE" ADD ATTRIBUTE horsepower NUMBER CASCADE
/
ALTER TYPE "APPUSER"."CAR_INFO_TYPE" ADD ATTRIBUTE color VARCHAR2(20) CASCADE
/
CREATE TYPE "APPUSER"."CAR_TYPE" 
   UNDER VEHICLE_TYPE (
      car_info CAR_INFO_TYPE
);
/

Here you can see the two approaches compared to each other

First approach	Second approach
`CREATE TYPE "APPUSER"."VEHICLE_TYPE"`	`CREATE TYPE "APPUSER"."VEHICLE_TYPE"`
`CREATE TYPE "APPUSER"."CAR_INFO_TYPE"`	`CREATE TYPE "APPUSER"."CAR_INFO_TYPE"`
`CREATE TYPE "APPUSER"."CAR_TYPE"`	`ALTER TYPE "APPUSER"."CAR_INFO_TYPE"`
`ALTER TYPE "APPUSER"."CAR_INFO_TYPE"`	`ALTER TYPE "APPUSER"."CAR_INFO_TYPE"`
`ALTER TYPE "APPUSER"."CAR_INFO_TYPE"`	`CREATE TYPE "APPUSER"."CAR_TYPE"`

When I check the version of the types, I see that CAR_TYPE doesn’t evolve together with CAR_INFO_TYPE:

select o.name, t.version# 
from obj$ o, user$ u,type$ t 
where o.owner# = u.user# and o.oid$ = t.toid and u.name='APPUSER'
order by o.name, t.version#;

NAME             VERSION#
-------------------------
CAR_INFO_TYPE           1
CAR_INFO_TYPE           2
CAR_INFO_TYPE           3
CAR_TYPE                1
VEHICLE_TYPE            1

In contrast to the previous example, because I changed the order of the statements, CAR_TYPE is now still on version 1 (not 3 as previously).

When you alter a type, you decide whether you also want to update the rows in a table that uses that type.

If you choose to update the corresponding table data, it can take quite some time on a big table.
If you choose to skip the update, the alteration will be fast. However, when you need to read the data, the database needs to use the evolution information to determine how to read the data.

What Data Pump Does

Data Pump can recreate the types during a regular import and transportable tablespaces. But it will handle the types one-by-one:

Create VEHICLE_TYPE (and all alterations if there were any)
Then create CAR_INFO_TYPE and all alterations
Then create CAR_TYPE (and all alterations if there were any)

Now, the definition of the types is correct (it has the correct attributes and stuff). But the evolution of the types does not necessarily match that of the source database. Unless the types in the source database were created in the same order as Data Pump does, then the versions of the types might be different.

What Happens During a Regular Import

During a regular (not transportable) export, Data Pump always use external table as access method when there is an evolved type involved. This access method causes a CREATE TABLE <external table> ... AS SELECT which converts each row’s version of a type to the highest version. All rows in the dump file is now of the highest version of the type.

On import, Data Pump creates the types, and perhaps the versions of the types are different. When Data Pump loads the data into the table, all rows are on the same version because they were converted during export. The data access layer underneath Data Pump is now more lenient. If one type version is found with a matching hash code, then data can be imported safely.

Data Pump then checks for a matching object type using a hash code only. The version does not have to match. Here’s a query to get the hash code:

select o.name, t.version#, t.hashcode 
from obj$ o, user$ u, type$ t 
where o.owner# = u.user# and o.oid$ = t.toid and u.name='APPUSER'
order by o.name, t.version#;

What Happens During a Transportable Tablespace Import

In transportable tablespace mode, things are different. Data Pump does not load the data into a dump file. Instead, it is kept in the data files. To read the data correctly, you must have the correct type definitions and information on how they evolve.

If the type evolution doesn’t match those of the source database, then Data Pump can’t be sure the data is accurate and will issue an error. Using the above query, the type evolution must match on version and hash code.

Solution

This is not a bug in Data Pump. Data Pump ensures that your data is not corrupted during import. Due to a restriction in the implementation of type evolution, Data Pump is not able to completely recreate a type’s evolution and this might cause problems.

These are your options:

Option 1: Use Regular Data Pump Import

As described above, during a regular import Data Pump handles the types and dependent objects differently.

Perform transportable tablespace import.
Find types and dependent objects that Data Pump can’t import.
Import the affected types and objects using a regular Data Pump import.

Option 2: Recreate Types and Dependent Objects Without Data

In one case I worked on, a queue table used the evolved type. Before migration, the application team would ensure that the application processed all messages in the queue. Thus, at the time of migration, the queue was empty.

The transportable tablespace import failed to create the queue table due to evolved type.
After import, the DBA recreated the queue using DBMS_AQADMIN.
Also, the DBA granted permissions on the queue.

If a regular table used the evolved type, you would need to recreate the table using CREATE TABLE syntax instead.

Option 3: Recreate Types Without Evolution Before Export

Recreate the types in the source database in the same way as Data Pump will create it on the target database. One type at a time. First, the CREATE TYPE statement and any ALTER TYPE statements on the same type. Then next type, and the next, and the next…

If a dependent table or queue uses the evolved type, it can be quite cumbersome and time consuming to recreate the type in the source database.

Option 4: Create Type in Target Using Same Evolution Before Import

If you know the evolution history of the types in the source database (i.e. in which order the CREATE TYPE and ALTER TYPE statements were issued), then you can create the types manually in the target database and ensure they evolve in the same way. You must creat the types exactly in the same way (including number of attributes, the order they were added and so forth).

In target database, you create and alter types in the correct order. In order to do that you must also create the schema.

You verify that types are of the same version in source and target database. The hash code must be identical as well:

select o.name, t.version#, t.hashcode 
from obj$ o, user$ u, type$ t 
where o.owner# = u.user# and o.oid$ = t.toid and u.name='APPUSER'
order by o.name, t.version#;

You perform the transportable tablespace import.
Data Pump reports an error for the types that already exist. You ignore those errors. You can also exclude types from the import (previous step) using EXCLUDE=TYPES.
Ensure that dependent objects that use the affected types are valid. It could be other types, tables, or queues.

Conclusion

Evolved types can hit ORA-39218 or ORA-39216 during a transportable tablespace import. Implementation restrictions in object types cause these errors. Data Pump can’t evolve the types in the same way, and issues an error. You must find a different way of moving the types and dependent objects.

Happy migrating!

Appendix

References

Documentation: Object-Relational Developer’s Guide, Oracle Database 19c
Blog: Advanced Queuing In Oracle9i, oracle-base.com
Blog: Object Types, oracle-base.com
Blog: HOL 19c Migrate FTEX, mikedietrichde.com

Complete Test Case

For reference, here is the test case I used to research. Use our hands-on lab, Hitchhiker’s Guide for Upgrading to Oracle Database 19c.

1. Create Test Data

export ORAENV_ASK=NO
export ORACLE_SID=UP19
. oraenv
export ORAENV_ASK=YES

CONN / AS SYSDBA

DROP USER APPUSER CASCADE;
DROP TABLESPACE APPS INCLUDING CONTENTS AND DATAFILES;

CREATE TABLESPACE APPS;
CREATE USER APPUSER IDENTIFIED BY APPUSER DEFAULT TABLESPACE APPS;
ALTER USER APPUSER QUOTA UNLIMITED ON APPS;
GRANT CONNECT TO APPUSER;
GRANT CREATE TYPE TO APPUSER;
GRANT CREATE TABLE TO APPUSER;
GRANT AQ_ADMINISTRATOR_ROLE TO APPUSER;

CONN APPUSER/APPUSER

CREATE TYPE "APPUSER"."VEHICLE_TYPE" 
   IS OBJECT (
      make VARCHAR2 (40)
) NOT FINAL;
/

CREATE TYPE "APPUSER"."CAR_INFO_TYPE" 
   IS OBJECT (
      model VARCHAR2(40)
);
/

CREATE TYPE "APPUSER"."CAR_TYPE" 
   UNDER VEHICLE_TYPE (
      car_info CAR_INFO_TYPE
);
/

ALTER TYPE "APPUSER"."CAR_INFO_TYPE" ADD ATTRIBUTE horsepower NUMBER CASCADE
/
ALTER TYPE "APPUSER"."CAR_INFO_TYPE" ADD ATTRIBUTE color VARCHAR2(20) CASCADE
/

CREATE TABLE "APPUSER"."CARS" (
   id number, 
   car_info car_type
);

INSERT INTO "APPUSER"."CARS"
VALUES (1, APPUSER.CAR_TYPE('FORD', APPUSER.CAR_INFO_TYPE('FIESTA', 200, 'BLUE')));
COMMIT;

BEGIN
   DBMS_AQADM.CREATE_QUEUE_TABLE (
      queue_table        => 'APPUSER.CAR_QUEUE_TABLE',
      queue_payload_type => 'APPUSER.CAR_TYPE');

   DBMS_AQADM.CREATE_QUEUE (
      queue_name  => 'APPUSER.CAR_QUEUE',
      queue_table => 'APPUSER.CAR_QUEUE_TABLE');

   DBMS_AQADM.START_QUEUE (
      queue_name => 'APPUSER.CAR_QUEUE',
      enqueue    => TRUE);
END;
/

DECLARE
  l_enqueue_options     DBMS_AQ.enqueue_options_t;
  l_message_properties  DBMS_AQ.message_properties_t;
  l_message_handle      RAW(16);
  l_event_msg           APPUSER.CAR_TYPE;
BEGIN
  l_event_msg := APPUSER.CAR_TYPE('VOLVO', APPUSER.CAR_INFO_TYPE('V90', 400, 'GREY'));

  DBMS_AQ.enqueue(queue_name          => 'APPUSER.CAR_QUEUE',        
                  enqueue_options     => l_enqueue_options,     
                  message_properties  => l_message_properties,   
                  payload             => l_event_msg,             
                  msgid               => l_message_handle);

  COMMIT;
END;
/

conn / as sysdba
select o.name, o.oid$, t.hashcode, t.version# 
from obj$ o, user$ u,type$ t 
where o.owner# = u.user# and o.oid$ = t.toid and u.name='APPUSER'
order by o.name, t.version#;

2. Transportable Tablespace Using FTEX

mkdir /tmp/dpdir
rm /tmp/dpdir/*
mkdir /u02/oradata/CDB2/pdb2

sqlplus / as sysdba<<EOF
ALTER TABLESPACE USERS READ ONLY;
ALTER TABLESPACE APPS READ ONLY;
create or replace directory dpdir as '/tmp/dpdir';
grant read, write on directory dpdir to system;
EOF

export ORAENV_ASK=NO
export ORACLE_SID=CDB2
. oraenv
export ORAENV_ASK=YES
sqlplus / as sysdba<<EOF
   startup
   alter pluggable database PDB2 close;
   drop pluggable database PDB2 including datafiles;
   create pluggable database PDB2 admin user adm identified by adm file_name_convert=('pdbseed', 'pdb2');
   alter pluggable database PDB2 open;
   alter pluggable database PDB2 save state;
   alter session set container=PDB2;
   create directory mydir as '/tmp/dpdir';
   grant read, write on directory mydir to system;
   create public database link SOURCEDB connect to system identified by oracle using 'UP19';
EOF

export ORAENV_ASK=NO
export ORACLE_SID=UP19
. oraenv
export ORAENV_ASK=YES
sqlplus / as sysdba<<EOF
set line 300
select 'cp ' || file_name || ' /u02/oradata/CDB2/pdb2/df' || file_id as c1 from dba_data_files where tablespace_name in ('USERS', 'APPS');
EOF

#Copy the files

export ORAENV_ASK=NO
export ORACLE_SID=CDB2
. oraenv
export ORAENV_ASK=YES
impdp system/oracle@pdb2 network_link=sourcedb full=y \
transportable=always metrics=y exclude=statistics directory=mydir \
logfile=pdb2.log \
transport_datafiles='/u02/oradata/CDB2/pdb2/df4' \
transport_datafiles='/u02/oradata/CDB2/pdb2/df5'

sqlplus appuser/APPUSER@PDB2<<EOF
  select * from appuser.cars;
  select * from APPUSER.CAR_QUEUE_TABLE;
EOF

Tag: type

Understand Why Data Pump Produce ORA-39218 or ORA-39216 on Evolved Types