Re: recreate a table with index in anothor tablesapce

"Andy" <usmle96_at_yahoo.com> wrote in message news:bacee0b8.0406221209.30b7ee29_at_posting.google.com...
> I am a new user of Oracle using 10g. Got the problem when sub
> following sql statements:
>
> -- simple test
>
> create table mytest (
> a varchar2(30)
> );
>
> alter table mytest
> add constraint pk_a primary key(a);
>
> alter table mytest
> enable primary key using index
> tablespace users_indx
> pctfree 0;
>
> drop table mytest cascade constraints;
>
> sqlplus tells second alter table has error:
> ora-00955: name is already used by another existing object.

Yes, because the mere fact of adding a primary key constraint to the table causes an index to be created. So your statement:

alter table mytest add constraint pk_a primary key(a);

...means that an index "pk_a" has just been created.

The command "enable primary key using index tablespace X" means "please create me a new index, called whatever the constraint is named, in tablespace X". So Oracle tries to create an index called pk_a... but discovers your first command has already caused an object with that name to exist.

> At least I found if comment out the tablespace clause, everything runs
> fine.

I suspect that not to actually be the case, and I'll do a little test to prove the point:

SQL> create table mytest (a varchar2(30)); Table created.

SQL> alter table mytest add constraint pk_a primary key(a); Table altered.

SQL> alter table mytest enable primary key using index   2 tablespace users pctfree 0;
alter table mytest enable primary key using index *
ERROR at line 1:
ORA-00955: name is already used by an existing object

Fails, exactly as you said it would

SQL> alter table mytest enable primary key using index   2 pctfree 0;
alter table mytest enable primary key using index *
ERROR at line 1:
ORA-00955: name is already used by an existing object

Er, still fails... which is not quite what you claimed would happen. This is not actually surprising if it is because, as I mentioned, it has everything to do with the index already existing under the name you're attempting to create, and nothing to do with the tablespace clause.

I'm not sure exactly why you are attempting to do things the way you are, but if you want to "hijack" an existing index to support a new constraint, instead of having one created for you, you have to do things slightly differently:

SQL> drop table mytest;
Table dropped.

SQL> create table mytest (a varchar2(30)); Table created.

SQL> create index myidx on mytest(a);
Index created.

SQL> alter table mytest add constraint pk_a primary key(a) using index myidx;
Table altered.

An alternative which has been around since 9i is:

create table mytest (a varchar2(30) constraint pk_a primary key using index (create index myidx on mytest(a) tablespace users));

That is, the create table statement can contain a complete nested create index statement within itself.

Regards
HJR
>may you explain what is wrong with this statement? and how to
> fix it?
>
> Thanks very much.
Received on Tue Jun 22 2004 - 15:52:55 CDT