Re: Create indexes in specific tablespace

From: Mark Bole <makbo_at_pacbell.net>
Date: Thu, 10 Nov 2005 03:05:06 -0800
Message-ID: <43732962.5050606@pacbell.net>

malcolm arnold wrote:

>>How can I amend the following to ensure any idexes are created in a
>>tablespaced called INDEXES, rather than the default tablespace?

>
>
> You have to create the table, then add the primary key in separate DDLs:
>
> CREATE TABLE TBLCOLUMNS(
> ID INTEGER NOT NULL ,
> SQLTYPE INTEGER ,
> PSIZE INTEGER ,
> COLTYPE VARCHAR2(50) ,
> NULABLE VARCHAR2(3) ,
> NAME VARCHAR2(20) ,
> ID_PARENT INTEGER)
>
> alter table TBLCOLUMNS
> add PRIMARY KEY (ID)
> using index tablespace my_tablespace
>
> alter table TBLCOLUMNS
> add FOREIGN KEY (ID_PARENT) REFERENCES TABLTABLE(ID)
>

Or, you can put it all in one statement (referencing tablespace INDEXES), see the example in the docs.

http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96540/clauses3a.htm#1002835

You can create the index with whatever storage parameters you want directly within the create table statement. Name the index (constraint) yourself to avoid using system-generated names.

-- 
Mark Bole
http://www.bincomputing.com



--
http://www.freelists.org/webpage/oracle-l

Received on Thu Nov 10 2005 - 05:07:19 CST