Hello,
I have following task. We have one big partitioned table and need to move some partition to backup database. The idea was to run in backup database
insert into table1_BCK select * from table1@dblink partition (abc)
And after insert drop source partition
The problem is that we get an error, the reason is that the source table has some columns as user defined type like
create type cust_type as varray(13) of number;
The same type exist also in backup database, the source and target tables DDLs are exactly the same.
Could you suggest some easy solution how to move partition from source to target without need of parsing or retyping these varrays?
thanks
Jaroslav
The concept of "the same" when it comes to types is more than just definition, it also needs a unique object ID. eg
remote database
=============
SQL> create or replace
2 type mytype as object
3 ( c1 int,
4 c2 int,
5 c3 int);
6 /
Type created.
SQL>
SQL> create table mytab
2 ( pk int,
3 o mytype);
Table created.
SQL>
SQL> insert into mytab
2 select rownum, mytype(rownum,rownum,rownum)
3 from dual
4 connect by level <= 10;
10 rows created.
SQL> commit;
Commit complete.
SQL> select *
2 from user_types
3 where type_name = 'MYTYPE'
4 @pr
==============================
TYPE_NAME : MYTYPE
TYPE_OID : 9E0400FB8D0D4AB59D5CD2D562B8963B
TYPECODE : OBJECT
ATTRIBUTES : 3
METHODS : 0
PREDEFINED : NO
INCOMPLETE : NO
FINAL : YES
INSTANTIABLE : YES
PERSISTABLE : YES
SUPERTYPE_OWNER :
SUPERTYPE_NAME :
LOCAL_ATTRIBUTES :
LOCAL_METHODS :
TYPEID :
PL/SQL procedure successfully completed.
and then on the local database
SQL> select * from mytab@db19pdb1;
select * from mytab@db19pdb1
*
ERROR at line 1:
ORA-22804: remote operations not permitted on object tables or user-defined type columns
SQL> create or replace
2 type mytype oid '9E0400FB8D0D4AB59D5CD2D562B8963B' as object <<<===== USE THE SAME OID
3 ( c1 int,
4 c2 int,
5 c3 int);
6 /
Type created.
SQL>
SQL> select * from mytab@db19pdb1;
PK
----------
O(C1, C2, C3)
------------------------------------------------------------------------------------------------
1
MYTYPE(1, 1, 1)
2
MYTYPE(2, 2, 2)
3
MYTYPE(3, 3, 3)
4
MYTYPE(4, 4, 4)
5
MYTYPE(5, 5, 5)
6
MYTYPE(6, 6, 6)