Re: Q: recreate a table with index in anothor tablesapce

Andy wrote:

> 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.
>
> At least I found if comment out the tablespace clause, everything runs
> fine. may you explain what is wrong with this statement? and how to
> fix it?
>
> Thanks very much.

Following up on Howard's great advice ... I'd suggest in the future using the following syntax:

ALTER TABLE mytest
ADD CONSTRAINT pk_a
PRIMARY KEY (a)
USING INDEX
PCTFREE 0
TABLESPACE ...;

-- 
Daniel Morgan
http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp
http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp
damorgan_at_x.washington.edu
(replace 'x' with a 'u' to reply)