Redefining a table with a user-defined type using DBMS_REDEFINITION

dbms-redefinitionoracleplsql

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, 

BEGIN
   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);
END;

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:

CREATE TYPE employee_t AS OBJECT
(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;

CREATE TYPE employee_t AS OBJECT
(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:

begin
for cr in (select * from department)
loop
dbms_output.put_line(cr.emp.employee_name);
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);
commit;
end loop;
end;
/

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.

Best Answer

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.

Related Question