Adding column(s) to a table
Syntax #1
To add a column to an existing table, the ALTER TABLE syntaxis:
ALTER TABLE table_name
ADD column_name column-definition;
For example:
ALTER TABLE supplier
ADD supplier_name varchar2(50);
This will add a column called supplier_name to thesupplier table.
Syntax #2
To add multiple columns to an existing table, the ALTER TABLEsyntax is:
ALTER TABLE table_name ADD ( column_1 column-definition,
column_2 column-definition,
...
column_n column_definition );
For example:
ALTER TABLE supplier ADD ( supplier_name varchar2(50),
city varchar2(45) );
This will add two columns (supplier_name andcity) to the supplier table.
Modifying column(s) in a table
Syntax #1
To modify a column in an existing table, the ALTER TABLE syntaxis:
ALTER TABLE table_name
MODIFY column_name column_type;
For example:
ALTER TABLE supplier
MODIFY supplier_name varchar2(100) not null;
This will modify the column called supplier_name to bea data type of varchar2(100) and force the column to not allow nullvalues.
Syntax #2
To modify multiple columns in an existing table, the ALTER TABLEsyntax is:
ALTER TABLE table_name MODIFY ( column_1 column_type,
column_2 column_type,
...
column_n column_type );
For example:
ALTER TABLE supplier MODIFY ( supplier_name varchar2(100) not null,
city varchar2(75)
);
This will modify both the supplier_name andcity columns.
Drop column(s) in a table
Syntax #1
To drop a column in an existing table, the ALTER TABLE syntaxis:
ALTER TABLE table_name
DROP COLUMN column_name;
For example:
ALTER TABLE supplier
DROP COLUMN supplier_name;
This will drop the column called supplier_name from thetable called supplier.
Rename column(s) in a table
(NEW in Oracle 9i Release 2)
Syntax #1
Starting in Oracle 9i Release 2, you can now rename acolumn.
To rename a column in an existing table, the ALTER TABLE syntaxis:
ALTER TABLE table_name
RENAME COLUMN old_name to new_name;
For example:
ALTER TABLE supplier
RENAME COLUMN supplier_name to sname;
This will rename the column called supplier_name tosname.