Oracle7 Server Administrator's Guide

Oracle7 Server Administrator's Guide

Altering Tables

To alter a table, the table must be contained in your schema, or you must have either the ALTER object privilege for the table or the ALTER ANY TABLE system privilege.

A table in an Oracle database can be altered for the following reasons:

to add one or more new columns to the table

to add one or more integrity constraints to a table

to modify an existing column's definition (datatype, length, default value, and NOT NULL integrity constraint)

to modify data block space usage parameters (PCTFREE, PCTUSED)

to modify transaction entry settings (INITRANS, MAXTRANS)

to modify storage parameters (NEXT, PCTINCREASE)

to enable or disable integrity constraints or triggers associated with the table

to drop integrity constraints associated with the table

You can increase the length of an existing column. However, you cannot decrease it unless there are no rows in the table. Furthermore, if you are modifying a table to increase the length of a column of datatype CHAR, realize that this may be a time consuming operation and may require substantial additional storage, especially if the table contains many rows. This is because the CHAR value in each row must be blank-padded to satisfy the new column length.

When altering the data block space usage parameters (PCTFREE and PCTUSED) of a table, note that new settings apply to all data blocks used by the table, including blocks already allocated and subsequently allocated for the table. However, the blocks already allocated for the table are not immediately reorganized when space usage parameters are altered, but as necessary after the change.

When altering the transaction entry settings (INITRANS, MAXTRANS) of a table, note that a new setting for INITRANS only applies to data blocks subsequently allocated for the table, while a new setting for MAXTRANS applies to all blocks (already and subsequently allocated blocks) of a table.

The storage parameters INITIAL and MINEXTENTS cannot be altered. All new settings for the other storage parameters (for example, NEXT, PCTINCREASE) affect only extents subsequently allocated for the table. The size of the next extent allocated is determined by the current values of NEXT and PCTINCREASE, and is not based on previous values of these parameters.

You can alter a table using the SQL command ALTER TABLE. The following statement alters the EMP table:

ALTER TABLE emp

   PCTFREE 30

   PCTUSED 60;

Warning: Before altering a table, familiarize yourself with the consequences of doing so:

If a new column is added to a table, the column is initially null. You can add a column with a NOT NULL constraint to a table only if the table does not contain any rows.

If a view or PL/SQL program unit depends on a base table, the alteration of the base table may affect the dependent object.

See Also: See

[*]

for information about how Oracle manages dependencies.