Change of Object ID

[EP]

Hello

Is there any way to change object ID ?
I create user type and can't import data as in the source database the type
has a different id

TIA
Regards
EP

[Jusung Yang]

Actually the question you should ask is : how do I exp/imp a table of
object type from one schema to another. You don't want to mess with
ORACLE internal stuff such as Object ID, even if you can find a way to
do that.

The problem is when you import the table of object type, imp will try
to create the object type first. OK here. But at the same time imp
also specifies the object ID, which is the ID of the object type in
the exp source schema. Since you can not have two objects in the same
db instance with the same ID, hence the error. You would not have this
problem if the 2 schemas are in 2 different databases.

I would consider this to be an ORACLE imp bug.

How to get around this, if you do want to do object type tables
imp/exp within the same db instance.

1. Create the involved types in a publicly accessible schema, say
system. For example:

create or replace type address_o as object (season varchar2(10),
address
varchar2(100));
create or replace type address_t as table of address_o;
create or replace type nT_type as object (name varchar2(30), address
address_t);
grant all on address_o to public;
grant all on address_t to public;
grant all on nT_type to public;

2. In your source schema, create your tables based on this type in the
system schema. For example:
create table home of system.nt_type
nested table address store as home_table;
3. Populate the table and export it.
4. Create the table in destination schema.
5. Do imp with ignore=y

A bit cumbersome, if you ask me. It makes me wonder why you are doing
what you are doing. Are you just experimenting?

"EP" <e...@plusnet.pl> wrote in message news:<alhp2p$ph2$1...@news2.ipartners.pl>...