Re: Create indexes in specific tablespace

  • From: Mark Bole <makbo@xxxxxxxxxxx>
  • To: oracle-l@xxxxxxxxxxxxx
  • Date: Thu, 10 Nov 2005 03:05:06 -0800

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



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


Other related posts:

  1. Home
  2. oracle-l
  3. Archive
  4. 11-2005