MODIFYING THE STRUCTURE OF TABLES

The structure of a table can be modified by using the ALTER TABLE command. ALTER TABLE allows changing the structure of an existing table. With ALTER TABLE it is, possible to add or delete columns, create or destroy indexes, change the data type of existing columns, or rename columns or the table itself.

ALTER TABLE works by making a temporary copy of the original table. The alteration is performed on the copy, then the original table is deleted and the new one is renamed. While ALTER TABLE is executing, the original table is still readable by users of Oracle.

Updates and writes to the table are stalled (slowed down) until the new table is ready, and then are automatically redirected to the new table without any failed updates.

Adding New Columns

Syntax:

ALTER TABLE

            Add (  (Size),

                        , (Size)‑)...);

Example :

Enter a new field called City in the table BRANCH_MSTR.

ALTER TABLE BRANCH_MSTR ADD (CITY VARCHAR2(25));

Output:

Table altered.

Dropping A Column From A Table

Syntax:

ALTER TABLE DROP COLUMN ;

Example :

Drop the column city from the BRANCH_MSTR table.

ALTER TABLE BRANCH_MSTR DROP COLUMN CITY;

Output:

Table altered.

Modifying Existing Columns

Syntax:

ALTER TABLE

MODIFY ( ());

Example :

Alter the BRANCH_MSTR table to allow the NAME field to hold maximum of 30 characters

ALTER TABLE BRANCH_MSTR MODIFY (NAME varchar2(30));

Output:

Table altered.

Restrictions on the ALTER TABLE

The following tasks cannot be performed when using the ALTER TABLE clause:

Ø  Change. the name of the table

Ø  Change the name of the column

Ø  Decrease the size of a column if table data exists