Re: oracle create index

"John" <nihal_at_newmexico.com> wrote in message news:bfhrvk$m52$1_at_reader2.nmix.net...
> I'm new to Oracle, there may be an obvious answer to my problem, but I
cant
> seem to figure it out. I'm importing over a mysql database to oracle. A
> number of my tables have indexes that have the same name.
>
> Example of what I am trying to do:
>
> create table mytable1 ( myid NUMBER(10),otherid NUMBER(10));
> create table mytable2 (someid NUMBER(10),otherid NUMBER(10));
>
> create INDEX otherid ON mytable1 (otherid);
> create INDEX otherid ON mytable2 (otherid);
>
> error: ORA-00955: name is already used by an existing object
>
> Can anyone suggest a way to accomplish this?
>
> Thanks!
>
>

Hi John. In case you are not aware, Oracle puts the names of all indexes in a namespace
and requires that every index's name be unique within their schema within this namespace.
Since you are most likely creating all your objects in a single schema [i.e. your own
schema] then all your index names must be unique.

You don't say how you generated the [Oracle compatible] DDL from your MySQL database. If you are converting by hand then you will have your work cut out for you.
What you have to do is make the index names unique.

1 You could concatenate the table name with the index name as follows:

     mytable1_otherid
2. Alternatively, you cound simply number your indexes within each table as follows:

     mytable1_i01

Remember that Oracle restricts you to 30 characters. Longer identifiers wiil produce
an error.

I hope I have not stated the obvious.

Cheers Received on Mon Jul 21 2003 - 18:30:16 CDT