Re: oracle create index

Ouch, I was worried this would be the answer. I have a script written that could easily build all the indexes with the table name concatinated on the front or something of the like, however what I am trying to avoid is rewriting all the other software that makes queries using these index names.

If I build each table and indexes into it's own schema, will this solve my problem and will this create other problems?

Thanks for the help.

"Chris O" <itoys1 -a t- optusnet -d o t- com -d o t- au> wrote in message news:3f1c7788$0$20080$afc38c87_at_news.optusnet.com.au...
> "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 - 23:34:48 CDT