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;
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?
  • 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.