Tablespace to store index (primary key and Intermedia) 1

[tjoyner]

I have created tables with primary keys. When I query USER_INDEXES, I find the primary key indexes are in the tablespace USERS. Should they be stored automatically in the tablespace INDX? If so, what do I need to do differently to get them there?

On the same theme, should intermedia indexes be stored in the DRSYS tablespace. I tried to specify tablespace DRSYS during CREATE INDEX and received an error stating this was an invalid option for domain indexes.

My USERS tablespace is filling up and I feel that both of the above scenarios are contributing to this problem. USERS has unlimited expansion, but I want to do it the correct way.

Dont assume, state the obvious. Any help would be appreciated.

 

[JimHorton]

Tjoyner,
In general, you should always have indexes in a separate tablespace from tables, etc. and not in User tablespace. As far as intermediate sized (if this is what you mean?) indexes & tables, a typical example would be to have small, med, and large tablespaces for respectively sized tables and indexes. A commonly used table, such as a customer table, might have it's own tablespace and the dba could put that tablespace on the outer edge sectors of a (or several) physical disks, for example to minimize disk-head movement when reading that table.
--Jim

 

[jee]

Sorry, but I don't know anything about Intermedia, so I can't help you there.

In general, if you do not specify TABLESPACE clauses in your CREATE TABLE statement, all data and indexes will go into the default tablespace for the owner of the table (must be tablespace USERS in your case). If you want the index created for the PK to go into a different tablespace, you must do something like this:

CREATE TABLE employee (
   emp_number   NUMBER (10) 
                CONSTRAINT PK_employee PRIMARY KEY
                USING INDEX TABLESPACE INDX,
   emp_name     VARCHAR2 (20));