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
Enter a new field called City in the table BRANCH_MSTR.
ALTER TABLE BRANCH_MSTR ADD (CITY VARCHAR2(25));
Dropping A Column From A Table
DROP COLUMN ;
Drop the column city from the BRANCH_MSTR table.
ALTER TABLE BRANCH_MSTR DROP COLUMN CITY;
Modifying Existing Columns
Alter the BRANCH_MSTR table to allow the NAME field to hold maximum of 30 characters
ALTER TABLE BRANCH_MSTR MODIFY (NAME varchar2(30));
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