Re: INDEX Use. How?

Karen,

Each Oracle user will have a default tablespace which will generally be the tablespace in which the users tables are to be placed. Unfortunately, indexes will also be placed in this tablespace if the 'create index' statement has not been qualified with the index tablespace name.

eg. create index indexname on tablename (columnname) tablespace indextblspacename;

Tables with primary or unique keys will need to also specify the index location;

eg      create table tablename (
              col1    varchar2(20) primary key using index tablespace
indextblspacename,
             col2     varchar2(50)
        );

Hope this helps

Regards,
Mick

Mick McRae
Oracle DBA
CSIRO Information Technology Services
mick.mcrae_at_its.csiro.au

Karen Byrd wrote:

> We want to establish two tablespaces(one for tables and the other for indices).
> If we give users the privileges to create an index via "creat index"
> or create tables using a primary key how can we guarantee where the
> indices are created by default? In other words we want all indices to
> reside in the tablespace we are going to set up for them every time.
>
> TIA
>
> --
> Karen Byrd
> Univ. of PA, School of Med.
> Computing and Information Technology
> byrd_at_mscf.med.upenn.edu
Received on Thu Feb 05 1998 - 00:00:00 CST