We use ALTER TABLE ADD COLUMN command to add columns to an existing table. Using this command we can add a single column or multiple columns at once. We can even specify NOT NULL clause as well as DEFAULT clause.

You can add columns to an table using  ALTER TABLE command only if you are the owner of the table. You can modify structure of other users table only if the table's owner has granted you the permission to modify structure of his tables. Otherwise Oracle will return insufficient privileges error.

EXAMPLES

How to add a single column to an existing table.

Let's say suppose we have a table by name "emp" with the following structure.

Now we want to add a column "city" to this table. To do that, we will issue the following command

alter table emp add city varchar2(20);

After issuing the above command the table structure will look like this

To fill the values in CITY column we need to use UPDATE command to set city values for different employees. Like

update emp set city='New York' where empno=7782

update emp set city='Dallas' where empno=7934

How to alter table add multiple columns to an existing table

If you want to add multiple columns to employee table you can give the following statement

For example, to add  addr,  pin, ph, fax to employees table we will give the following command

alter table emp add (addr varchar2(20),
      pin varchar2(10),ph varchar2(20));

How about adding columns with NOT NULL constraint

As we have seen above, when we add a column, the column will be empty and we need to issue UPDATE statement to fill the values in that column. So the question is, Can we add a column with NOT NULL constraint? The answer is yes, we can add a column with NOT NULL constraint,  but, there must 2 conditions which needs to be met 

The table must either be empty or if the table is not empty then, you must also specify the DEFAULT value.

If you give the following command when table is not empty, you will get the following error

alter table emp add (city varchar2(100) not null);

However, if you also specify default value then it will succeed

alter table emp add (city varchar2(100) default "Paris' not null);

Alter table adding a PRIMARY KEY column

You can also add a column to an existing table with PRIMARY KEY constraint only if the table is empty and if there is no PRIMARY KEY already existing in the table.

To add a column with PRIMARY KEY constraint, give the following command

alter table emp add (EmpID varchar2(20) constraint emp_pk primary key);

If you try to add a column with PRIMARY KEY constraint and if the table is not empty then, it will through the following error

 or if there is already a column with primary key constraint then, Oracle will through the following error

How to ALTER TABLE MODIFY columns in Oracle

How to ALTER TABLE DROP column in Oracle