index space

Richard Ross -

I know that I am coming late to your question, and several people have already answered, (at least one correctly), but I don't think it has been made clear what happens when you CREATE an index, or add a primary key or unique constraint, which also creates an index under the covers.

If no tablespace is explicitly specified for the index, it will be created in the tablespace designated as the creating user's DEFAULT TABLESPACE. The default tablespace is set by the CREATE USER or ALTER USER command, and if not specified, it will be the SYSTEM tablespace, a very poor choice indeed. However, since your user probably has no quota granted in SYSTEM, he/she could not create the index there, either explicitly or by default.

When Oracle creates an index, it must sort the index keys. If the amount of data to be sorted is at all large, Oracle will use temporary space in the database for intermediate sort storage. The temporary space will be allocated in the tablespace designated as the creating user's TEMPORARY TABLESPACE. The default temporary tablespace is also set by the CREATE USER or ALTER USER command, and if not specified, it will also be the SYSTEM tablespace, once again a very poor choice. Worse, the user DOES NOT NEED TO HAVE A QUOTA in a tablespace to be used for temporary storage, including SYSTEM. Since you are running out of space in SYSTEM, Oracle has defaulted to SYSTEM for the temporary tablespace. The proper way to fix this is to ALTER the user to set her/his TEMPORARY TABLESPACE to another tablespace. Most Oracle7 databases have a tablespace named TEMP that is specifically designed to be used for this purpose. I suggest you point ALL of your users' temporary tablespaces at TEMP. Received on Tue Mar 14 1995 - 09:58:07 CST