Oracle ALTER TABLE

Summary: in this tutorial, you will learn how to use the Oracle ALTER TABLE statement to modify the table structure.

To modify the structure of an existing table, you use the ALTER TABLE statement. The following illustrates the syntax:

ALTER TABLE table_name action;
Code language: SQL (Structured Query Language) (sql)

In this statement:

  • First, specify the table name which you want to modify.
  • Second, indicate the action that you want to perform after the table name.

The ALTER TABLE statement allows you to:

  • Add one or more columns
  • Modify column definition
  • Drop one or more columns
  • Rename columns
  • Rename table

Let’s see some examples to understand how each action works.

Oracle ALTER TABLE examples

We will use the persons table that we created in the previous tutorial for the demonstration.

Oracle ALTER TABLE ADD column examples

To add a new column to a table, you use the following syntax:

ALTER TABLE table_name ADD column_name type constraint;
Code language: SQL (Structured Query Language) (sql)

For example, the following statement adds a new column named birthdate to the persons table:

ALTER TABLE persons ADD birthdate DATE NOT NULL;
Code language: SQL (Structured Query Language) (sql)

If you view the persons table, you will see that the birthdate column is appended at the end of the column list:

DESC persons; Name Null Type ---------- -------- ------------ PERSON_ID NOT NULL NUMBER FIRST_NAME NOT NULL VARCHAR2(50) LAST_NAME NOT NULL VARCHAR2(50) BIRTHDATE NOT NULL DATE
Code language: SQL (Structured Query Language) (sql)

To add multiple columns to a table at the same time, you place the new columns inside the parenthesis as follows:

ALTER TABLE table_name ADD ( column_name type constraint, column_name type constraint, ... );
Code language: SQL (Structured Query Language) (sql)

See the following example:

ALTER TABLE persons ADD ( phone VARCHAR(20), email VARCHAR(100) );
Code language: SQL (Structured Query Language) (sql)

In this example, the statement added two new columns named phone and email to the persons table.

DESC persons Name Null Type ---------- -------- ------------- PERSON_ID NOT NULL NUMBER FIRST_NAME NOT NULL VARCHAR2(50) LAST_NAME NOT NULL VARCHAR2(50) BIRTHDATE NOT NULL DATE PHONE VARCHAR2(20) EMAIL VARCHAR2(100)
Code language: SQL (Structured Query Language) (sql)

Oracle ALTER TABLE MODIFY column examples

To modify the attributes of a column, you use the following syntax:

ALTER TABLE table_name MODIFY column_name type constraint;
Code language: SQL (Structured Query Language) (sql)

For example, the following statement changes the birthdate column to a null-able column:

ALTER TABLE persons MODIFY birthdate DATE NULL;
Code language: SQL (Structured Query Language) (sql)

Let’s verify the persons table structure again:

DESC persons Name Null Type ---------- -------- ------------- PERSON_ID NOT NULL NUMBER FIRST_NAME NOT NULL VARCHAR2(50) LAST_NAME NOT NULL VARCHAR2(50) BIRTHDATE DATE PHONE VARCHAR2(20) EMAIL VARCHAR2(100)
Code language: SQL (Structured Query Language) (sql)

As you can see, the birthdate became null-able.

To modify multiple columns, you use the following syntax:

ALTER TABLE table_name MODIFY ( column_1 type constraint, column_1 type constraint, ...);
Code language: SQL (Structured Query Language) (sql)

For example, the following statement changes the phone and email column to NOT NULLcolumns and extends the length of the email column to 255 characters:

ALTER TABLE persons MODIFY( phone VARCHAR2(20) NOT NULL, email VARCHAR2(255) NOT NULL );
Code language: SQL (Structured Query Language) (sql)

Verify the persons table structure again:

DESC persons; Name Null Type ---------- -------- ------------- PERSON_ID NOT NULL NUMBER FIRST_NAME NOT NULL VARCHAR2(50) LAST_NAME NOT NULL VARCHAR2(50) BIRTHDATE DATE PHONE NOT NULL VARCHAR2(20) EMAIL NOT NULL VARCHAR2(255)
Code language: SQL (Structured Query Language) (sql)

Oracle ALTER TABLE DROP COLUMN example

To remove an existing column from a table, you use the following syntax:

ALTER TABLE table_name DROP COLUMN column_name;
Code language: SQL (Structured Query Language) (sql)

This statement deletes the column from the table structure and also the data stored in that column.

The following example removes the birthdate column from the persons table:

ALTER TABLE persons DROP COLUMN birthdate;
Code language: SQL (Structured Query Language) (sql)

Viewing the persons table structure again, you will find that the birthdate column has been removed:

DESC persons; Name Null Type ---------- -------- ------------- PERSON_ID NOT NULL NUMBER FIRST_NAME NOT NULL VARCHAR2(50) LAST_NAME NOT NULL VARCHAR2(50) PHONE NOT NULL VARCHAR2(20) EMAIL NOT NULL VARCHAR2(255)
Code language: SQL (Structured Query Language) (sql)

To drop multiple columns at the same time, you use the syntax below:

ALTER TABLE table_name DROP (column_1,column_2,...);
Code language: SQL (Structured Query Language) (sql)

For example, the following statement removes the phone and email columns from the persons table:

ALTER TABLE persons DROP ( email, phone );
Code language: SQL (Structured Query Language) (sql)

Let’s check the persons table again:

DESC persons; Name Null Type ---------- -------- ------------ PERSON_ID NOT NULL NUMBER FIRST_NAME NOT NULL VARCHAR2(50) LAST_NAME NOT NULL VARCHAR2(50)
Code language: SQL (Structured Query Language) (sql)

The email and phone columns have been removed as expected.

Oracle ALTER TABLE RENAME column example

Since version 9i, Oracle added a clause for rename a column as follows:

ALTER TABLE table_name RENAME COLUMN column_name TO new_name;
Code language: SQL (Structured Query Language) (sql)

For example, the following statement renames the first_name column to forename column:

ALTER TABLE persons RENAME COLUMN first_name TO forename;
Code language: SQL (Structured Query Language) (sql)

The following statement checks the result:

DESC persons; Name Null Type --------- -------- ------------ PERSON_ID NOT NULL NUMBER FORENAME NOT NULL VARCHAR2(50) LAST_NAME NOT NULL VARCHAR2(50)
Code language: SQL (Structured Query Language) (sql)

Oracle ALTER TABLE RENAME table example

To give a table a new name, you use the following syntax:

ALTER TABLE table_name RENAME TO new_table_name;
Code language: SQL (Structured Query Language) (sql)

For example, the statement below renames the persons table to people table:

ALTER TABLE persons RENAME TO people;
Code language: SQL (Structured Query Language) (sql)

In this tutorial, you have learned how to use the Oracle ALTER TABLE statement to change the structure of an existing table.

Was this tutorial helpful?