Oracle Alter Table

What is Alter Statement: Alter statement is the part of DDL (Data Definition Language). Which is used to change (Alter) the definition of a table.

Use of Alter Statement (Applicable to only existing table)

  • Rename of Table
  • Add column
  • Update Column
  • Drop Column
  • Create Unique Key
  • Create Primary Key
  • Create Index
  • Enable / Disable Trigger

 

Rename a Table

Syntax for Renaming a table

ALTER TABLE <table_name> RENAME TO <new_table_name>;

Example of renaming a table (From JBT to Javabeginnerstutorial)

ALTER TABLE jbt RENAME TO javabeginnerstutorial;

 

Add a Column to existing table

Syntax for adding a Single Column

ALTER TABLE <table_name> ADD COLUMN <column_name> <column_type>

Example for adding a column in table

ALTER TABLE jbt ADD COLUMN owner
varchar2(20);

Here owner column of varchar type is added to jbt table.

 

Syntax for adding a Multiple Column

ALTER TABLE <table_name> add

(<column_name> <column_type>,

<column_name> <column_type>,

<column_name> <column_type>

…..)

Example for adding Multiple column in table

ALTER TABLE jbt ADD COLUMN

(owner
varchar2(20), name varchar2(20));

Update an existing column to table

ALTER TABLE <table_name> UPDATE COLUMN <column_name> <new_column_type>

Example for updating an existing column

ALTER TABLE jbt UPDATE COLUMN owner
number;

 

Drop an existing column from Table

ALTER TABLE <table_name> DROP COLUM <column_name>;

 

By | 2017-07-21T21:43:34+00:00 July 29th, 2013|Oracle|0 Comments

About the Author:

Leave A Comment