Oracle Tutorial

  • Home
  • Start Here
  • Basics
  • Advanced
    • Oracle View
    • Oracle Index
    • Oracle Synonym
    • Oracle Sequence
    • Oracle Administration
  • PL/SQL
  • Functions
    • Aggregate Functions
    • Analytic Functions
    • Comparison Functions
    • Date Functions
    • String Functions
  • API
    • Python Oracle
Home / Oracle Basics / Oracle ALTER TABLE

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;

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;

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

ALTER TABLE persons ADD birthdate DATE NOT NULL;

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

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, ... );

See the following example:

ALTER TABLE persons ADD ( phone VARCHAR(20), email VARCHAR(100) );

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)

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;

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

ALTER TABLE persons MODIFY birthdate DATE NULL;

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)

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, ...);

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 );

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)

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;

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;

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)

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

ALTER TABLE table_name DROP (column_1,column_2,...);

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

ALTER TABLE persons DROP ( email, phone );

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)

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;

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

ALTER TABLE persons RENAME COLUMN first_name TO forename;

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)

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;

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

ALTER TABLE persons RENAME TO people;

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?
  • YesNo
Previous Oracle CREATE TABLE
Next Oracle ALTER TABLE ADD Column By Examples

Getting Started

  • What Is Oracle Database
  • Install Oracle Database Server
  • Download Oracle Sample Database
  • Create Oracle Sample Database
  • Connect To Oracle Database Server

Oracle Data Manipulation

  • SELECT
  • Oracle DUAL Table
  • ORDER BY
  • SELECT DISTINCT
  • WHERE
  • Table & Column Aliases
  • AND
  • OR
  • FETCH
  • BETWEEN
  • IN
  • LIKE
  • IS NULL
  • Joins
  • INNER JOIN
  • LEFT JOIN
  • RIGHT JOIN
  • FULL OUTER JOIN
  • CROSS JOIN
  • Self Join
  • GROUP BY
  • HAVING
  • UNION
  • INTERSECT
  • MINUS
  • GROUPING SETS
  • CUBE
  • ROLLUP
  • PIVOT
  • UNPIVOT
  • INSERT
  • INSERT INTO SELECT
  • INSERT ALL
  • UPDATE
  • DELETE
  • MERGE
  • Subquery
  • Correlated Subquery
  • EXISTS
  • NOT EXISTS
  • ANY
  • ALL

Oracle Data Types

  • Oracle Data Types
  • NUMBER
  • FLOAT
  • BINARY_FLOAT
  • CHAR
  • NCHAR
  • VARCHAR2
  • NVARCHAR2
  • DATE
  • INTERVAL
  • TIMESTAMP
  • TIMESTAMP WITH TIME ZONE

Oracle Data Definition

  • CREATE TABLE
  • Identity Column
  • ALTER TABLE
  • ALTER TABLE ADD Column
  • ALTER TABLE MODIFY Column
  • Drop Columns
  • DROP TABLE
  • TRUNCATE TABLE
  • RENAME Table
  • Oracle Virtual Column

Oracle Constraints

  • PRIMARY KEY
  • FOREIGN KEY
  • UNIQUE
  • CHECK
  • NOT NULL

Oracle Views

  • CREATE VIEW
  • DROP VIEW
  • Updatable Views
  • Inline Views
  • WITH CHECK OPTION

About Oracle Tutorial

OracleTututorial.com website provides Developers and Database Administrators with the updated Oracle tutorials, scripts, and tips.

Search

Recent Tutorials

  • Oracle Implicit Statement Results
  • Calling PL/SQL Stored Functions in Python
  • Calling PL/SQL Procedures in Python
  • Managing Transaction in Python
  • Deleting Data From Oracle Database in Python

Site Links

  • Oracle Books
  • About
  • Contact
  • Privacy Policy
  • Terms of Use

Copyright © 2021 Oracle Tutorial. All Rights Reserved.