We are using Oracle 9.2.

We have a big table which has a user-defined objects. I need to move it to
another database. "Insert .. select * from.." does not work. What I am
doing now is "Insert .. select non_object column" to move all the
non-user_defined objects. Then I made a big file, containing huge amount of
sql statements, to update the user-defined objects.

The object is like this

SQL> desc Parent_OBJTYPE
Name Null? Type
----------------------------------------- -------- ------------------------
----
Col1 COl1_OBJTYPE
Col2 Col2_OBJTYPE

SQL> desc Col1_OBJTYPE
Name Null? Type
----------------------------------------- -------- -------------------------
-
Col1_1 VARCHAR2(255)
Col1_2 VARCHAR2(30)

SQL> desc Col2_OBJTYPE
Name Null? Type
----------------------------------------- -------- -------------------------
-
Col2_1 VARCHAR2(255)
Col2_2 VARCHAR2(30)

To update the table, I use

update table test
set Parent_Col=Parent_OBJTYPE(Col1_OBJTYPE ('test1','test2'),
Col2_OBJTYPE ('test3','test4'))
where id=1;

There are several millions of records to update.

Is there any other better way of doing this?

Thanks for your help.