# ALTER TABLE in Oracle

ALTER TABLE
To add, modify, drop or delete columns in a table ALTER TABLE statement is used. Along with all these, it is also used to rename a table. The ALTER TABLE statement allow the users to Add one or more columns, to Modify column definition, to Drop one or more columns, to Rename columns and to Rename a table.

Syntax:

ALTER TABLE table_name action;


Syntax: To add a column in the existing table.

ALTER TABLE table_name


Parameters:
table_name: It is used to specify the name of the table.
column_definition: It is used to specify the column name, data type, and its constraint.

Syntax: To add multiple columns in the existing table.

ALTER TABLE table_name
column_2 column_definition,
...
column_n column_definition);


ALTER TABLE students ADD student_age varchar(20);

Explanation:
A new column “student_age” of data type VARCHAR and a maximum length of 20 will be added in the “students” table.

ALTER TABLE students ADD student_age varchar(20); ADD student_city varchar(30);

Explanation:
Two new columns “student_age” and “student_city” of data type VARCHAR and a maximum length of 20 and 30 will be added in the “students” table.

ALTER TABLE MODIFY column
Syntax: To modify a single column of a table.

ALTER TABLE table_name
MODIFY column_name action;


Syntax: To modify multiple columns of a table.

ALTER TABLE table_name
MODIFY (
column_name_1 action,
column_name_2 action,
...
);


Example 1: Modifying a single column of a table.

ALTER TABLE students MODIFY student_name varchar2(50) NOT NULL;

Explanation:
Here the “students” is an already existing table whose column student_name is modified to varchar2 (50) and the column is now restricted to not allow NULL values.

Example 2: Modifying multiple columns of a table.

ALTER TABLE students MODIFY (student_name varchar2(50) NOT NULL; student_age varchar2(40));

Explanation:
Here the “students” is an already existing table whose column student_name is modified to varchar2 (50) and the column is now restricted to not allow NULL values. Similarly, the column student_age is modified to varchar2 (40).

ALTER TABLE DROP COLUMN
Syntax:

ALTER TABLE table_name
DROP COLUMN column_name;


Example :

ALTER TABLE students DROP COLUMN student_age;

Explanation:
Here, “students” is an already existing table, from which the student_age column will be dropped.

ALTER TABLE RENAME COLUMN
Syntax:

ALTER TABLE table_name
RENAME COLUMN existing_column_name to new_column_name;


Example :

ALTER TABLE students RENAME COLUMN student_age to std_age;

Explanation:
Here, “students” is an already existing table. The column student_age will be renamed as std_age.

ALTER TABLE RENAME TO

Syntax:

ALTER TABLE existing_table_name
RENAME TO new_name;


Example :

ALTER TABLE students RENAME TO children;

Explanation:
Here, “students” is an already existing table. The table name will be renamed as “children”.