Re: Where can I find the description of an ORA Error number !!

Kevin LEE wrote:

> Hi all:
>
> I have the following error mesg from the oracle database:
>
> ORA-01654: unable to extend index KLEE.SYS_C001410 by 315 in tablespace
> SYSTEM
>
> I suspect the above erreor is due to my table is reached to it full
> size and cannot accept any new data.
>
> I want to known where can I find the exact description of ORA-01654: on
> line ???

What you have is some type of index-based constraint that is trying to expand in the SYSTEM tablespace. query dba_indexes for the index name of SYS_C001410 and determine what the constraint is doing. Just based on past personal experience I'm going to bet that this is a Primary Key constraint and that it was created by someone logged in as SYSTEM even though the KLEE owner was specifically used during the creation of the Primary Key constraint.

There are a couple things that tend to happen that cause this type of thing. Many Oracle users have a really nasty habit of logging in as the DBA to create tables for a particular schema. While they are dutiful about fully qualifying the name of the table, they tend to forget that the moment they designate a column as a primary key, they are also creating an index. Because no name was given for the index, a system generated name is used. But, since the default tablespace for SYSTEM is the SYSTEM tablespace, the primary key index gets created there instead of in the appropriate tablespace for indexes. The way around this problem is to log in as the owner of the schema that will own the table. But, beyond that it is important to correctly define the primary key constraint. Here is an example:

ALTER TABLE MYTABLE
ADD CONSTRAINT PK_MYTABLE
PRIMARY KEY (COL1)
USING INDEX

STORAGE (INITIAL     50K
                   NEXT          50K
                   PCTINCREASE 0)