Oracle7 Server Administrator's Guide

Contents Index Home Previous Next

Altering Indexes

To alter an index, your schema must contain the index or you must have the ALTER ANY INDEX system privilege. You can alter an index only to change the transaction entry parameters or to change the storage parameters; you cannot change its column structure.

Alter the storage parameters of any index, including those created by Oracle to enforce primary and unique key integrity constraints, using the SQL command ALTER INDEX. For example, the following statement alters the EMP_ENAME index:

ALTER INDEX emp_ename
   INITRANS 5
   MAXTRANS 10
   STORAGE (PCTINCREASE 50);

When you alter the transaction entry settings (INITRANS, MAXTRANS) of an index, a new setting for INITRANS only applies to data blocks subsequently allocated, while a new setting for MAXTRANS applies to all blocks (already and subsequently allocated blocks) of an index.

The storage parameters INITIAL and MINEXTENTS cannot be altered. All new settings for the other storage parameters affect only extents subsequently allocated for the index.

For indexes that implement integrity constraints, you can also adjust storage parameters by issuing an ALTER TABLE statement that includes the ENABLE clause with the USING INDEX option. For example, the following statement changes the storage options of the index defined in the previous section:

ALTER TABLE emp
   ENABLE PRIMARY KEY USING INDEX
   PCTFREE 5;


Contents Index Home Previous Next