Oracle / PLSQL: ALTER TABLE Statement - TechOnTheNet

1. Home
2. Oracle / PLSQL
3. Tables

Oracle / PLSQL: ALTER TABLE Statement

This Oracle tutorial explains how to use the Oracle ALTER TABLE statement to add a column, modify a column, drop a column, rename a column or rename a table (with syntax, examples and practice exercises).

Description

The Oracle ALTER TABLE statement is used to add, modify, or drop/delete columns in a table. The Oracle ALTER TABLE statement is also used to rename a table.

Add column in table

Syntax

To ADD A COLUMN in a table, the Oracle ALTER TABLE syntax is:

ALTER TABLE table_name ADD column_name column_definition;

Example

Let's look at an example that shows how to add a column in an Oracle table using the ALTER TABLE statement.

For example:

ALTER TABLE customers ADD customer_name varchar2(45);

This Oracle ALTER TABLE example will add a column called customer_name to the customers table that is a data type of varchar2(45).

In a more complicated example, you could use the ALTER TABLE statement to add a new column that also has a default value:

ALTER TABLE customers ADD city varchar2(40) DEFAULT 'Seattle';

In this example, the column called city has been added to the customers table with a data type of varchar2(40) and a default value of 'Seattle'.

Add multiple columns in table

Syntax

To ADD MULTIPLE COLUMNS to an existing table, the Oracle ALTER TABLE syntax is:

ALTER TABLE table_name ADD (column_1 column_definition, column_2 column_definition, ... column_n column_definition);

Example

Let's look at an example that shows how to add multiple columns in an Oracle table using the ALTER TABLE statement.

For example:

ALTER TABLE customers ADD (customer_name varchar2(45), city varchar2(40) DEFAULT 'Seattle');

This Oracle ALTER TABLE example will add two columns, customer_name as a varchar2(45) field and city as a varchar2(40) field with a default value of 'Seattle' to the customers table.

Modify column in table

Syntax

To MODIFY A COLUMN in an existing table, the Oracle ALTER TABLE syntax is:

ALTER TABLE table_name MODIFY column_name column_type;

Example

Let's look at an example that shows how to modify a column in an Oracle table using the ALTER TABLE statement.

For example:

ALTER TABLE customers MODIFY customer_name varchar2(100) NOT NULL;

This Oracle ALTER TABLE example will modify the column called customer_name to be a data type of varchar2(100) and force the column to not allow null values.

In a more complicated example, you could use the ALTER TABLE statement to add a default value as well as modify the column definition:

ALTER TABLE customers MODIFY city varchar2(75) DEFAULT 'Seattle' NOT NULL;

In this example, the ALTER TABLE statement would modify the column called city to be a data type of varchar2(75), the default value would be set to 'Seattle' and the column would be set to not allow null values.

Modify Multiple columns in table

Syntax

To MODIFY MULTIPLE COLUMNS in an existing table, the Oracle ALTER TABLE syntax is:

ALTER TABLE table_name MODIFY (column_1 column_type, column_2 column_type, ... column_n column_type);

Example

Let's look at an example that shows how to modify multiple columns in an Oracle table using the ALTER TABLE statement.

For example:

ALTER TABLE customers MODIFY (customer_name varchar2(100) NOT NULL, city varchar2(75) DEFAULT 'Seattle' NOT NULL);

This Oracle ALTER TABLE example will modify both the customer_name and city columns. The customer_name column will be set to a varchar2(100) data type and not allow null values. The city column will be set to a varchar2(75) data type, its default value will be set to 'Seattle', and the column will not allow null values.

Drop column in table

Syntax

To DROP A COLUMN in an existing table, the Oracle ALTER TABLE syntax is:

ALTER TABLE table_name DROP COLUMN column_name;

Example

Let's look at an example that shows how to drop a column in an Oracle table using the ALTER TABLE statement.

For example:

ALTER TABLE customers DROP COLUMN customer_name;

This Oracle ALTER TABLE example will drop the column called customer_name from the table called customers.

Rename column in table (NEW in Oracle 9i Release 2)

Syntax

Starting in Oracle 9i Release 2, you can now rename a column.

To RENAME A COLUMN in an existing table, the Oracle ALTER TABLE syntax is:

ALTER TABLE table_name RENAME COLUMN old_name TO new_name;

Example

Let's look at an example that shows how to rename a column in an Oracle table using the ALTER TABLE statement.

For example:

ALTER TABLE customers RENAME COLUMN customer_name TO cname;

This Oracle ALTER TABLE example will rename the column called customer_name to cname.

Rename table

Syntax

To RENAME A TABLE, the Oracle ALTER TABLE syntax is:

ALTER TABLE table_name RENAME TO new_table_name;

Example

Let's look at an example that shows how to rename a table in Oracle using the ALTER TABLE statement.

For example:

ALTER TABLE customers RENAME TO contacts;

This Oracle ALTER TABLE example will rename the customers table to contacts.

Practice Exercise #1:

Based on the departments table below, rename the departments table to depts.

CREATE TABLE departments ( department_id number(10) NOT NULL, department_name varchar2(50) NOT NULL, CONSTRAINT departments_pk PRIMARY KEY (department_id) );

Solution for Practice Exercise #1:

The following Oracle ALTER TABLE statement would rename the departments table to depts:

ALTER TABLE departments RENAME TO depts;

Practice Exercise #2:

Based on the employees table below, add a column called bonus that is a number(6) datatype.

CREATE TABLE employees ( employee_number number(10) NOT NULL, employee_name varchar2(50) NOT NULL, department_id number(10), CONSTRAINT employees_pk PRIMARY KEY (employee_number) );

Solution for Practice Exercise #2:

The following Oracle ALTER TABLE statement would add a bonus column to the employees table:

ALTER TABLE employees ADD bonus number(6);

Practice Exercise #3:

Based on the customers table below, add two columns - one column called contact_name that is a varchar2(50) datatype and one column called last_contacted that is a date datatype.

CREATE TABLE customers ( customer_id number(10) NOT NULL, customer_name varchar2(50) NOT NULL, address varchar2(50), city varchar2(50), state varchar2(25), zip_code varchar2(10), CONSTRAINT customers_pk PRIMARY KEY (customer_id) );

Solution for Practice Exercise #3:

The following Oracle ALTER TABLE statement would add the contact_name and last_contacted columns to the customers table:

ALTER TABLE customers ADD (contact_name varchar2(50), last_contacted date);

Practice Exercise #4:

Based on the employees table below, change the employee_name column to a varchar2(75) datatype.

CREATE TABLE employees ( employee_number number(10) NOT NULL, employee_name varchar2(50) NOT NULL, department_id number(10), CONSTRAINT employees_pk PRIMARY KEY (employee_number) );

Solution for Practice Exercise #4:

The following Oracle ALTER TABLE statement would change the datatype for the employee_name column to varchar2(75):

ALTER TABLE employees MODIFY employee_name varchar2(75);

Practice Exercise #5:

Based on the customers table below, change the customer_name column to NOT allow null values and change the state column to a varchar2(2) datatype.

CREATE TABLE customers ( customer_id number(10) NOT NULL, customer_name varchar2(50), address varchar2(50), city varchar2(50), state varchar2(25), zip_code varchar2(10), CONSTRAINT customers_pk PRIMARY KEY (customer_id) );

Solution for Practice Exercise #5:

The following Oracle ALTER TABLE statement would modify the customer_name and state columns accordingly in the customers table:

ALTER TABLE customers MODIFY (customer_name varchar2(50) NOT NULL, state varchar2(2));

Practice Exercise #6:

Based on the employees table below, drop the salary column.

CREATE TABLE employees ( employee_number number(10) NOT NULL, employee_name varchar2(50) NOT NULL, department_id number(10), salary number(6), CONSTRAINT employees_pk PRIMARY KEY (employee_number) );

Solution for Practice Exercise #6:

The following Oracle ALTER TABLE statement would drop the salary column from the employees table:

ALTER TABLE employees DROP COLUMN salary;

Practice Exercise #7:

Based on the departments table below, rename the department_name column to dept_name.

CREATE TABLE departments ( department_id number(10) NOT NULL, department_name varchar2(50) NOT NULL, CONSTRAINT departments_pk PRIMARY KEY (department_id) );

Solution for Practice Exercise #7:

The following Oracle ALTER TABLE statement would rename the department_name column to dept_name in the departments table:

ALTER TABLE departments RENAME COLUMN department_name TO dept_name; NEXT: Drop Table Share on:

Databases

• SQL
• Oracle / PLSQL
• SQL Server
• MySQL
• MariaDB
• PostgreSQL
• SQLite

MS Office

• Excel
• Access
• Word

Web Development

• HTML
• CSS
• JavaScript
• Color Picker

Programming

• C Language

More

• ASCII
• Unicode
• Linux
• UNIX
• Techie Humor

Oracle Basics

• ALIASES
• AND
• AND & OR
• BETWEEN
• COMPARISON OPERATORS
• DELETE
• DISTINCT
• EXISTS
• FROM
• GROUP BY
• HAVING
• IN
• INSERT
• INSERT ALL
• INTERSECT
• IS NOT NULL
• IS NULL
• JOIN
• LIKE
• MINUS
• NOT
• OR
• ORDER BY
• PIVOT
• REGEXP_LIKE
• SELECT
• SUBQUERY
• TRUNCATE
• UNION
• UNION ALL
• UPDATE
• WHERE

Oracle Advanced

• Alter Table
• Alter Tablespace
• Change Password
• Check Constraints
• Comments in SQL
• Create Schema
• Create Schema Statement
• Create Table
• Create Table As
• Create Tablespace
• Create User
• Data Types
• Declare Variables
• Drop Table
• Drop Tablespace
• Drop User
• Error Messages
• Find Default Tablespace
• Find Users
• Find Users Logged In
• Find Version Information
• Functions
• Global Temporary
• Grant/Revoke Privileges
• Indexes
• Literals
• Local Temporary
• Primary Keys
• Procedures
• Roles
• Sequences
• Set Default Tablespace
• Synonyms
• System Tables
• Unique Constraints
• Views

Oracle Cursors

• Close Cursor
• Cursor Attributes
• Declare Cursor
• Fetch Cursor
• Open Cursor
• Select For Update
• Where Current Of

Oracle Exception Handling

• Named Programmer-Defined Exception
• Named System Exception
• WHEN OTHERS Clause
• SQLCODE
• SQLERRM

Oracle Foreign Keys

• Disable Foreign Key
• Drop Foreign Key
• Enable Foreign Key
• Foreign Key
• Foreign Key (cascade delete)
• Foreign Key (set null delete)

Oracle Loops/Conditionals

• CURSOR FOR LOOP
• EXIT
• FOR LOOP
• GOTO
• IF-THEN-ELSE
• LOOP
• REPEAT UNTIL LOOP
• WHILE LOOP

Oracle Transactions

• Commit Transaction
• Lock Table
• Rollback Transaction
• Set Transaction

Oracle Triggers

• After Delete Trigger
• After Insert Trigger
• After Update Trigger
• Before Delete Trigger
• Before Insert Trigger
• Before Update Trigger
• Disable All Triggers
• Disable Trigger
• Drop Trigger
• Enable All Triggers
• Enable Trigger

String/Char Functions

• ASCII
• ASCIISTR
• CHR
• COMPOSE
• CONCAT
• Concat with ||
• CONVERT
• DECOMPOSE
• DUMP
• INITCAP
• INSTR
• INSTR2
• INSTR4
• INSTRB
• INSTRC
• LENGTH
• LENGTH2
• LENGTH4
• LENGTHB
• LENGTHC
• LOWER
• LPAD
• LTRIM
• NCHR
• REGEXP_INSTR
• REGEXP_REPLACE
• REGEXP_SUBSTR
• REPLACE
• RPAD
• RTRIM
• SOUNDEX
• SUBSTR
• TRANSLATE
• TRIM
• UPPER
• VSIZE

Numeric/Math Functions

• ABS
• ACOS
• ASIN
• ATAN
• ATAN2
• AVG
• BITAND
• CEIL
• COS
• COSH
• COUNT
• EXP
• FLOOR
• GREATEST
• LEAST
• LN
• LOG
• MAX
• MEDIAN
• MIN
• MOD
• POWER
• REGEXP_COUNT
• REMAINDER
• ROUND (numbers)
• ROWNUM
• SIGN
• SIN
• SINH
• SQRT
• SUM
• TAN
• TANH
• TRUNC (numbers)

Date/Time Functions

• ADD_MONTHS
• CURRENT_DATE
• CURRENT_TIMESTAMP
• DBTIMEZONE
• EXTRACT
• LAST_DAY
• LOCALTIMESTAMP
• MONTHS_BETWEEN
• NEW_TIME
• NEXT_DAY
• ROUND (dates)
• SESSIONTIMEZONE
• SYSDATE
• SYSTIMESTAMP
• TRUNC (dates)
• TZ_OFFSET

Conversion Functions

• BIN_TO_NUM
• CAST
• CHARTOROWID
• FROM_TZ
• HEXTORAW
• NUMTODSINTERVAL
• NUMTOYMINTERVAL
• RAWTOHEX
• TO_CHAR
• TO_CLOB
• TO_DATE
• TO_DSINTERVAL
• TO_LOB
• TO_MULTI_BYTE
• TO_NCLOB
• TO_NUMBER
• TO_SINGLE_BYTE
• TO_TIMESTAMP
• TO_TIMESTAMP_TZ
• TO_YMINTERVAL

Analytic Functions

• CORR
• COVAR_POP
• COVAR_SAMP
• CUME_DIST
• DENSE_RANK
• FIRST_VALUE
• LAG
• LAST_VALUE
• LEAD
• LISTAGG
• NTH_VALUE
• RANK
• STDDEV
• VAR_POP
• VAR_SAMP
• VARIANCE

Advanced Functions

• BFILENAME
• CARDINALITY
• CASE
• COALESCE
• DECODE
• EMPTY_BLOB
• EMPTY_CLOB
• GROUP_ID
• LNNVL
• NANVL
• NULLIF
• NVL
• NVL2
• SYS_CONTEXT
• UID
• USER
• USERENV

Home | About Us | Contact Us | Testimonials | Donate

While using this site, you agree to have read and accepted our Terms of Service and Privacy Policy.

Copyright © 2003-2025 TechOnTheNet.com. All rights reserved.