SQL ALTER

The structure of the table can be modified by using ALTER TABLE command. 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.

1. ALTER TABLE changing the structure of an existing table
2. ALTER TABLE add or delete columns
3. ALTER TABLE create or destroy indexes
4. ALTER TABLE change the data type of existing columns
5. ALTER TABLE rename columns or table itself

Note

1. To use ALTER TABLE, the ALTER, INSERT and CREATE privileges for the table are required
2. While ALTER TABLE IS executing, the original table is still readable by users of the oracle

Adding new columns

Syntax

ALTER TABLE <TableName> ADD(<NewColumnName 1> <DataType> (<Size>), 
<NewColumnName 2> <DataType> (<Size>), .............. )

Example

ALTER TABLE EMPLOYEE ADD (COUNTRY VARCHAR2(25));

Drop column from table

Syntax

ALTER TABLE <TableName> DROP COLUMN <ColumnName>

Example

ALTER TABLE  EMPLOYEE  DROP COLUMN COUNTRY

Modify existing column in table

Syntax

ALTER TABLE <TableName> MODIFY ( <ColumnName> <NewDataType> (<NewSize>));

Example

ALTER TABLE EMPLOYEE  MODIFY ( COUNTRY VARCHAR2(30));

Note

The following tasks can not be performed when using the ALTER TABLE clause.

1. Change the name of the column.
2. Change the name of the table
3. Decrease the size of a column if the table data exists

Narayanaswamy

Hello! I am Narayanaswamy founder and admin of narayanatutorial.com. I have been working in the IT industry for more than 12 years. NarayanaTutorial is my web technologies blog. My specialties are Java / J2EE, Spring, Hibernate, Struts, Webservices, PHP, Oracle, MySQL, SQLServer, Web Hosting, Website Development, and IAM(ForgeRock) Specialist

I am a self-learner and passionate about training and writing. I am always trying my best to share my knowledge through my blog.