Oracle Alter Table

Alter statement is the part of DDL (Data Definition Language). Which is used to change (Alter) the definition of a table.This tutorial is to provide details of Alter Statement in Oracle.

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

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

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

ALTER TABLE jbt UPDATE COLUMN owner
number;

ALTER TABLE emp MODIFY (emp_no varchar2(20) NOT NULL, sal number(6,2);

Renaming a column of a table:

Syntax:

ALTER TABLE emp RENAME COLUMN emp_id1 to emp_no;

Drop an existing column from Table

ALTER TABLE <table_name> DROP COLUM <column_name>;

Example

ALTER TABLE emp DROP COLUMN emp_no;

To drop column dept_id and the foreign key constraint dept_con of DEPT table.

Example

ALTER TABLE emp DROP (dept_id) CASCADE CONSTRAINTS;

Specifying Parallel Clause

Example

ALTER TABLE emp PARALLEL;

 Dropping a Table Partition

Example

ALTER TABLE emp DROP PARTITION emp_p3;

 Changing the State of a Constraint

Syntax

ALTER TABLE employees
   ENABLE VALIDATE CONSTRAINT empfk
   EXCEPTIONS INTO exceptions;

The constraint remains disable when any row violates the constraint.
You can identify the exceptions in the emp table by:

SELECT a.*
   FROM employees a, exceptions b
   WHERE e.rowid = b.row_id
      AND a.table_name = 'EMP'
      AND a.constraint = 'EMPFK';

To specify an exception table, you must have the privileges necessary to insert rows into the exception table.

To check identified exceptions, you must have the privileges necessary to query the exceptions table.

Renaming Table Partitions

Syntax:

ALTER TABLE emp RENAME PARTITION emp2 TO emp_part1;

Truncating Table Partitions

Syntax:

ALTER TABLE emp TRUNCATE PARTITION emp_part1 DROP STORAGE;

DROP STORAGE clause frees space.