Re: primary key index on separate tablespace not possible?

In article <393E4889.744DC7F7_at_snet.net>,   Richard Chen <qchen_at_snet.net> wrote:
> Thanks. That worked fine. I am surprised that "alter table ..."
> syntax does not support this.
>
> Regards
>
> Richard
>
> Kari Pannila wrote:
> > =
 

> > Hi
> > =
 

> > Create first the index and then the constraint:
> > =
 

> > CREATE UNIQUE INDEX PK_TABLE_1 ON TABLE_1
> > ( ASNO ASC );
> > =
 

> > ALTER TABLE TABLE_1
> > ADD ( CONSTRAINT PK_TABLE_1 PRIMARY KEY (ASNO) ) ;
> > =
 

> > Regards Kari
> > =
 

> > (Mr.) Kari Pannila
> > Oracle DBA (OCP)
> > --
> > =
 

> > PS. Delete X from my mail address , it (X) is only for SPAMMERS.
> > Poista X mail-osoitteen edest=E4, se on SPAMmereita varten.
> > =
 

> > =
 

> > Richard Chen wrote:
> > =
 

> > > We generally have tables and indexes on separate tablespaces.
> > > However, I find that the following does not work:
> > >
> > > alter table t_name add constraint t_name_pk primary key
 (column_name)=  

> > > tablespace ts_name;
> > >
> > > Without using "tablespace ts_name" the above works except that
> > > the index is created on the same tablespace as for the table
 itself.
> > >
> > > Can anyone confirm that this is true or my syntax is wrong.
> > >
> > > Thanks
> > >
> > > Richard
> > =
 

> > =
 

> >
>

Richard,

The alter table syntax DOES support this type of activity, as Eugenio attempted to explain to you. The statement you tried to use was syntactically incorrect; the correct statement should read as follows:

   alter table t_name add constraint t_name_pk primary key (column_name) USING INDEX tablespace ts_name;

The USING INDEX clause is supported for the alter table command. This tells Oracle to utilize the index tablespace provided for the primary key index. You need not create the index first and connect it to the primary key constraint; the statement above will do all of that for you.

David Fitzjarrell
Oracle DBA

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Wed Jun 07 2000 - 00:00:00 CDT