ALTER TABLE statement

Description

Modifies a table definition.

Syntax

ALTER TABLE [ owner.]table-name
{ alter-clause, ... }

Parameters

Examples

Example 1

Adds a new column to the Employees table showing which office they work in:

ALTER TABLE Employees
ADD office CHAR(20)

Example 2

Drops the office column from the Employees table:

ALTER TABLE Employees
DROP office

Example 3

Adds a column to the Customers table assigning each customer a sales contact:

ALTER TABLE Customers
ADD SalesContact INTEGER
REFERENCES Employees (EmployeeID)

Example 4

Adds a new column CustomerNum to the Customers table and assigns a default value of 88:

ALTER TABLE Customers
ADD CustomerNum INTEGER DEFAULT 88

Example 5

Only FP indexes for c2, c4 and c5, are moved from dbspace Dsp3 to Dsp6. FP index for c1 remains in Dsp1. FP index for c3 remains in Dsp2. The primary key for c5 remains in Dsp4. Date index c4_date remains in Dsp5.

CREATE TABLE foo (
		c1 INT IN Dsp1,
		c2 VARCHAR(20),
		c3 CLOB IN Dsp2,
		c4 DATE,
		c5 BIGINT,
		PRIMARY KEY (c5) IN Dsp4) IN Dsp3;

	CREATE DATE INDEX c4_date ON foo(c4) IN Dsp5;
	ALTER TABLE foo
		MOVE TO Dsp6;

Example 6

Moves only FP index c1 from dbspace Dsp1 to Dsp7.

ALTER TABLE foo ALTER c1 MOVE TO Dsp7

Example 7

This example illustrates the use of many ALTER TABLE clauses to move, split, rename, and merge partitions.

Create a partitioned table:

CREATE TABLE bar (
		c1 INT,
		c2 DATE,
		c3 VARCHAR(10))
	PARTITION BY RANGE(c2)
	(p1 VALUES <= ('2005-12-31') IN dbsp1,
	 p2 VALUES <= ('2006-12-31') IN dbsp2,
	 P3 VALUES <= ('2007-12-31') IN dbsp3,
	 P4 VALUES <= ('2008-12-31') IN dbsp4);

	INSERT INTO bar VALUES(3, '2007-01-01', 'banana nut');
	INSERT INTO BAR VALUES(4, '2007-09-09', 'grape jam');
	INSERT INTO BAR VALUES(5, '2008-05-05', 'apple cake');

Move partition p2 to dbsp5:

ALTER TABLE bar MOVE PARTITION p2 TO DBSP5;

Split partition p4 into 2 partitions:

ALTER TABLE bar SPLIT PARTITION p4 INTO 
		(P41 VALUES <= ('2008-06-30') IN dbsp4,
		 P42 VALUES <= ('2008-12-31') IN dbsp4);

The following SPLIT PARTITION reports an error as it requires data movement. Not all existing rows will be in the same partition after split.

ALTER TABLE bar SPLIT PARTITION p3 INTO 
		(P31 VALUES <= ('2007-06-30') IN dbsp3,
		 P32 VALUES <= ('2007-12-31') IN dbsp3);

The following error is reported:

"No data move is allowed, cannot split partition p3."

The following SPLIT PARTITION reports an error, because it changes the partition boundary value.

ALTER TABLE bar SPLIT PARTITION p2 INTO 
		(p21 VALUES <= ('2006-06-30') IN dbsp2,
		 P22 VALUES <= ('2006-12-01') IN dbsp2);

The following error is reported:

"Boundary value for the partition p2 cannot be changed."

Merge partition p3 into p2. An error is reported as a merge from a higher boundary value partition into a lower boundary value partition is not allowed.

ALTER TABLE bar MERGE PARTITION p3 into p2;

The following error is reported:

"Partition 'p2' is not adjacent to or before partition 'p3'."

Merge partition p2 into p3:

ALTER TABLE bar MERGE PARTITION p2 INTO P3;

Rename partition p1 to p1_new:

ALTER TABLE bar RENAME PARTITION p1 TO p1_new;

Unpartition table bar:

ALTER TABLE bar UNPARTITION;

Partition table bar. This command reports an error, because all rows must be in the first partition.

ALTER TABLE bar PARTITION BY RANGE(c2)
	(p1 VALUES <= ('2005-12-31') IN dbsp1,
	 P2 VALUES <= ('2006-12-31') IN DBSP2,
	 P3 VALUES <= ('2007-12-31') IN dbsp3,
	 P4 VALUES <= ('2008-12-31') IN dbsp4);

The following error is reported:

"All rows must be in the first partition."

Partition table bar:

ALTER TABLE bar PARTITION BY RANGE(c2)
	(p1 VALUES <= ('2008-12-31') IN dbsp1,
	 P2 VALUES <= ('2009-12-31') IN dbsp2,
	 P3 VALUES <= ('2010-12-31') IN dbsp3,
	 P4 VALUES <= ('2011-12-31') IN dbsp4);

Usage

The ALTER TABLE statement changes table attributes (column definitions and constraints) in a table that was previously created. The syntax allows a list of alter clauses; however, only one table constraint or column constraint can be added, modified, or deleted in each ALTER TABLE statement.

	CREATE TABLE foo (c1 INT, c2 INT)
		PARTITION BY RANGE (c1)
		(P1 VALUES <= (100) IN dbsp1,
		 P2 VALUES <= (200) IN dbsp2,
		 P3 VALUES <= (MAX) IN dbsp3
		) IN dbsp4);
	LOAD TABLE ….
	ALTER TABLE DROP PARTITION P1;

ALTER TABLE is prevented whenever the statement affects a table that is currently being used by another connection. ALTER TABLE can be time consuming, and the server does not process requests referencing the same table while the statement is being processed.

Each table object can reside in only one dbspace. Any type of ALTER MOVE blocks any modification to the table for the entire duration of the move.

You must have DBA authority or have CREATE privilege on the new dbspace and be the table owner or have alter permission on the table.

• Automatic commit. The ALTER and DROP options close all cursors for the current connection. The DBISQL data window is also cleared.

• A checkpoint is carried out at the beginning of the ALTER TABLE operation.

• Once you alter a column or table, any stored procedures, views or other items that refer to the altered column no longer work.

Standards

Permissions

Must have DBA authority or CREATE permission on the new dbspace and be the table owner or have ALTER permission on the table. Requires exclusive access to the table.

See also