The application which we developed has one schema, USER1, which has tables, types, packages etc. I have to move some of the user-defined types to a different schema, The reason for doing this is to enable to editions for user1 to suppport online patching of the application. USER2. USER1 has few tables which have a dependency on these user-defined types. We recreated the types in USER2, but before I drop the types in USER1, we have to remove the table's dependency on them. To do this, I am thinking of using DBMS_REDEFINITION by creating an intermediate table using the user defined type in USER2 (instead of USER1)

I'm using the following to cast the user defined type in USER1 and to the new one created in USER2,

   DBMS_REDEFINITION.START_REDEF_TABLE( 'user1', 'department', 'department_in'
         , 'dept_id dept_id, dept_name dept_name
         , cast(emp as user2.employee_t) emp'
         , dbms_redefinition.cons_use_rowid);

But, I'm getting the following error:

Error : ORA-22907: invalid CAST to a type that is not a nested table or VARRAY

Below are the details:

User1 has the following objects:

(employee_id NUMBER
,employee_name VARCHAR2(30)
,salary NUMBER
,dept_id NUMBER);

CREATE TABLE department
(emp employee_t
,mgr varchar2(40)
,dept_id number
,dept_name varchar2(30));

When we try to enable editions for User1, we get, we use the force option table will be come invalid.

ERROR at line 1: ORA-38819: user USER1 owns one or more objects whose type is editionable and that have noneditioned dependent objects

ALTER TYPE RESET for the type gives following error, when there are dependent tables.

ERROR at line 1: ORA-22374: cannot reset the version of a type with table dependents

I tried the following, a. Recreated the type in User2;

(employee_id NUMBER
,employee_name VARCHAR2(30)
,salary NUMBER
,dept_id NUMBER);

b. Created a temp table in user1 using the type in user2

CREATE TABLE department_tmp
(emp user2.employee_t
,mgr varchar2(40)
,dept_id number
,dept_name varchar2(30));

c. Migrated the data using the script:

for cr in (select * from department)
insert into department_tmp values (user2.employee_t(cr.emp.employee_id,cr.emp.employee_name,cr.emp.salary,cr.emp.dept_id),cr.mgr,cr.dept_id,cr.dept_name);
end loop;

d. dropped the type employee_t and table department in user1 e.renamed the table departmemt_tmp to department f. enabled editions user1, edition was enabled successfully.

Here I have taken a simple case, there are many such tables in our application (some with nest tables also), the above approach will not be easy. Hence I was exploring the possibility of using DBMS_REDEFINITION.

Please let me know if there is any way to achieve this.

1 Answer 1


alter type xxx noneditionable;

will allow you to have a table (non-editionable) reference your type (editionable by default) and still enable your schema for editions.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.