ALTER TABLE statement

Use the ALTER TABLE statement to modify the schema of an existing table.

Syntax

(1)
Basic Table Options

2.1+ ,
2.1  %ADD Column Clause4
2.1  %ADD AUDIT Clause5
2.1  %ADD CONSTRAINT Clause6
2.1  %Add or drop specialized columns7
2.1  %DROP AUDIT Clause8
2.1  %DROP CONSTRAINT Clause9
2.1  %DROP Column Clause10
2.1 11 %LOCK MODE Clause12
2.1  %MODIFY Clause13
2.1 11 %MODIFY EXTENT SIZE Clause14
2.1 11 %MODIFY NEXT SIZE Clause15
2.1  %PUT Clause16
2.1  %SECURITY POLICY Clause17
Notes:
Element Description Restrictions Syntax
synonym Synonym for the table to be altered Synonym and its table must exist; USETABLENAME must not be set Identifier
table Name of table to be altered Must exist in the current database Identifier

Usage

You can use the Basic Table Options segment to modify the schema of a table by adding, modifying, or dropping columns and constraints, or changing the extent size or locking granularity of a table. The database server performs alterations in the order that you specify. If any of the actions fails, the entire operation is canceled. You can associate an existing table with a named ROW type, or specify a new storage space to store large-object data. You can add or drop shadow columns to support secondary-server update operations of the USELASTCOMMITTED feature, or add or drop a rowid column. However, a single ALTER TABLE statement cannot specify these options with most other alterations to the schema of the table.

To use ALTER TABLE, your discretionary access privileges must meet at least one of the following conditions:
  • You must have DBA privilege on the database that contains the table.
  • You must own the table.
  • You must have the Alter privilege on the specified table and the Resource privilege on the database where the table resides.
  • To add a referential constraint, you must have the DBA or References privilege on either the referenced columns or the referenced table.
  • To drop a constraint, you must have the DBA privilege or be the owner of the constraint. If you are the owner of the constraint but not the owner of the table, you must have Alter privilege on the specified table. You do not need the References privilege to drop a constraint.

If you run a slow alter on a table that contains a compressed partition, then the corresponding new partition is compressed. The number of compressed rows in the new partition might differ from the number of compressed rows in the original partition. The difference is caused by the timing of operations to rewrite the rows in the partition and to build the compression dictionary. If the resulting new partition has fewer compressed rows, you can recompress the partition, and optionally repack and shrink it.

The ALTER TABLE statement cannot add a fragmentation strategy to a nonfragmented table, nor modify the storage distribution strategy of a fragmented table. To modify the distributed storage strategy of a table, you must use the ALTER FRAGMENT statement, rather than the ALTER TABLE statement. For information on adding, modifying, or dropping the storage distribution strategy of a table, see the ALTER FRAGMENT statement.

Altering a table on which a view depends might invalidate the view.
Warning: The clauses available with this statement have varying performance implications. Before you undertake alter operations, check information in the Altering a table definition section in your HCL OneDB™ Performance Guide to review effects and strategies.
The table whose name or synonym follows the ALTER TABLE keywords must be a permanent table in the current database. It is subject to the following restrictions:
  • It cannot be a temporary table.
  • It cannot be a table in a database that is not the current database.
  • It cannot be a table object that the CREATE EXTERNAL TABLE statement defined.
  • It cannot be a violations table or a diagnostics table.
  • If the USETABLENAME environment variable is set, you cannot specify a synonym for the table in the ALTER TABLE statement.

In addition, you cannot use the ALTER TABLE statement for the following operations:

  • Add, drop, or modify a column in a table that has an associated violation table or diagnostics table.
  • Define a referential constraint or a unique constraint on a RAW table.
  • Define an index on a column or on a set of columns that would conflict with the Restrictions on columns as index keys.