Oracle Alter Table

This tutorial is to provide details of Alter Statement in Oracle.

Alter table statement is a DDL statement.

Adding a new column to a table:

Syntax:

ALTER TABLE emp ADD COLUMN (sal number);

Renaming a column of a table:

Syntax:

ALTER TABLE emp RENAME COLUMN emp_id1 to emp_no;

 

Modifying columns of a table:

Syntax:

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

 

Dropping column of a table:

Syntax:

ALTER TABLE emp DROP COLUMN emp_no;

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

Syntax:

ALTER TABLE emp DROP (dept_id) CASCADE CONSTRAINTS;

 

Specifying Parallel Clause:

Syntax:

ALTER TABLE emp PARALLEL;

 Dropping a Table Partition:

Syntax:

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.

By | 2017-07-21T21:44:42+00:00 July 30th, 2012|Oracle|0 Comments

About the Author:

Leave A Comment