>>-ALTER TABLE--+-table---+------------------------------------->
                '-synonym-'
 
                             (1)
>--+-| Basic Table Options |-------+---------------------------><
   |                          (2)  |
   '-| Logging TYPE Options |------'
 

1. See page Basic Table Options
2. See page Logging TYPE Options

In Dynamic Server, the database server performs the actions in the ALTER TABLE statement in the order that you specify. If any action fails, the entire operation is cancelled.

Altering a table on which a view depends might invalidate the view.

You cannot alter a temporary table. You also cannot alter a violations or diagnostics table. In addition, you cannot add, drop, or modify a column if the table that contains the column has a violation table or a diagnostics table associated with it. If the USETABLENAME environment variable is set, you cannot specify a synonym for the table in the ALTER TABLE statement.

In Extended Parallel Server, if a table has range fragmentation, only the Logging TYPE options and LOCK MODE clause are valid. All other ALTER TABLE options return an error.

For a RAW or (in Extended Parallel Server) a STATIC table, the Logging TYPE options are the only part of the ALTER TABLE statement that you can use.

To use ALTER TABLE, you must meet one of the following conditions:

• You must have DBA privilege on the database containing the table.
• You must own the table (and for Extended Parallel Server, you must also have the Resource privilege).
• 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.

The Basic Table Options segment of ALTER TABLE has the following syntax.

Basic Table Options:
 
     .-,-------------------------------------------.
     V                  (1)                        |
|--+---+-| ADD Clause |--------------------------+-+-+----------|
   |   |                           (2)           |   |
   |   +-| ADD CONSTRAINT Clause |---------------+   |
   |   |                   (3)                   |   |
   |   +-| MODIFY Clause |-----------------------+   |
   |   |                            (4)          |   |
   |   +-| DROP CONSTRAINT Clause |--------------+   |
   |   |                 (5)                     |   |
   |   +-| DROP Clause |-------------------------+   |
   |   |  (6)                               (7)  |   |
   |   +--------| MODIFY NEXT SIZE Clause |------+   |
   |   |  (6)                        (8)         |   |
   |   +--------| LOCK MODE Clause |-------------+   |
   |   |  (9)                  (10)              |   |
   |   '--------| PUT Clause |-------------------'   |
   |  (9)                                            |
   '--------+-+-ADD--+--+-CRCOLS-+-------------+-----'
            | '-DROP-'  '-ROWIDS-'             |
            |  (9)                       (11)  |
            '--------| ADD TYPE Clause |-------'
 

1. See page ADD Clause
2. See page ADD CONSTRAINT Clause
3. See page MODIFY Clause (IDS)
4. See page DROP CONSTRAINT Clause
5. See page DROP Clause
6. Use path once
7. See page MODIFY NEXT SIZE Clause
8. See page LOCK MODE Clause
9. Dynamic Server only
10. See page PUT Clause (IDS)
11. See page ADD TYPE Clause (IDS)

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 cancelled.

With Dynamic Server, you can also associate a table with a named ROW type or specify a new storage space to store large-object data. You can also add or drop rowid columns and shadow columns for Enterprise Replication. You cannot, however, specify these options in conjunction with any other alterations.