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

"Andy" <usmle96_at_yahoo.com> wrote in message news:bacee0b8.0406230314.7a2e7bd1_at_posting.google.com...
> Daniel Morgan <damorgan_at_x.washington.edu> wrote in message
news:<1087947219.15823_at_yasure>...
> >
> > 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, thanks for advice. But I am in a situation not to modify the
> code I have for alter table.

When the code you have is using just plain wrong syntax, then you are not in a position to not modify the code. And yes, I'm aware that's a double negative. So let me make it clearer: the code *must* be modified.

Care to elaborate as to why your duff code is sacrosanct?

> And the problem I think is this:
>
> the index is created in another tablespace. When drop table, it may
> not be able to drop that index in that tablespace.

Rubbish. Dropping a table always drops associated indexes, and the tablespace they're stored in is irrelevant. A drop, after all, is merely the deletion of some rows from data dictionary tables in SYSTEM tablespace.

>When submit the
> statements again, sqlplus sees that index existing and generates
> error. I am writing a script, and try to drop that index in that
> tablespace while drop the table, this way I guess I won't see error.
> But, I don't know how to do this to drop that index in a different
> tablespace. Do you have some idea?

I don't know what it is about recent posters here. You show them the code. You tell them the facts. And they still refuse to believe it.

*The tablespace is irrelevant*. Your problem is that you (or your untouchable code) does not know how to create constraints on tables properly, or how to house the indexes associated with those constraints properly.

FIX YOUR CODE!!
HJR Received on Wed Jun 23 2004 - 06:22:05 CDT