ALTER TABLE v13
Name
ALTER TABLE
-- change the definition of a table.
Synopsis
where action
is one of:
Description
ALTER TABLE
changes the definition of an existing table. There are several subforms:
ADD column type
This form adds a new column to the table using the same syntax as
CREATE TABLE
.DROP COLUMN
This form drops a column from a table. Indexes and table constraints involving the column will be automatically dropped as well.
ADD table_constraint
This form adds a new constraint to a table; for details, see CREATE TABLE.
DROP CONSTRAINT
This form drops constraints on a table. Currently, constraints on tables are not required to have unique names, so there may be more than one constraint matching the specified name. All matching constraints will be dropped.
RENAME
The RENAME
forms change the name of a table (or an index, sequence, or view) or the name of an individual column in a table. There is no effect on the stored data.
The PARALLEL
clause sets the degree of parallelism for a table. The NOPARALLEL
clause resets the values to their defaults; reloptions
will show the parallel_workers
parameter as 0
.
A superuser has permission to create a trigger on any user's table but a user can create a trigger only on the table they own. However, when the ownership of a table is changed, the ownership of the trigger's implicit objects is updated when they are matched with a table owner owning a trigger.
You can use ALTER TRIGGER ...ON AUTHORIZATION
command to alter a trigger's implicit object owner, for information, see ALTER TRIGGER.
You must own the table to use ALTER TABLE
.
Parameters
name
The name (possibly schema-qualified) of an existing table to alter.
column
Name of a new or existing column.
new_column
New name for an existing column.
new_name
New name for the table.
type
Data type of the new column.
table_constraint
New table constraint for the table.
constraint_name
Name of an existing constraint to drop.
CASCADE
Automatically drop objects that depend on the dropped constraint.
PARALLEL
Specify PARALLEL
to select a degree of parallelism; you can also specify the degree of parallelism by setting the parallel_workers
parameter when performing a parallel scan on a table. If you specify PARALLEL
without including a degree of parallelism, the index will use default parallelism.
NOPARALLEL
Specify NOPARALLEL
to reset parallelism to default values.
integer
The integer
indicates the degree of parallelism, which is the number of parallel_workers
used in the parallel operation to perform a parallel scan on a table.
Notes
When you invoke ADD COLUMN
, all existing rows in the table are initialized with the column’s default value (null if no DEFAULT
clause is specified). Adding a column with a non-null default will require the entire table to be rewritten. This may take a significant amount of time for a large table; and it will temporarily require double the disk space. Adding a CHECK
or NOT NULL
constraint requires scanning the table to verify that existing rows meet the constraint.
The DROP COLUMN
form does not physically remove the column, but simply makes it invisible to SQL operations. Subsequent insert and update operations in the table will store a null value for the column. Thus, dropping a column is quick but it will not immediately reduce the on-disk size of your table, as the space occupied by the dropped column is not reclaimed. The space will be reclaimed over time as existing rows are updated.
Changing any part of a system catalog table is not permitted. Refer to CREATE TABLE for a further description of valid parameters.
Examples
To add a column of type VARCHAR2
to a table:
To drop a column from a table:
To rename an existing column:
To rename an existing table:
To add a check constraint to a table:
To remove a check constraint from a table:
To reset the degree of parallelism to 0 on the emp
table:
The following example creates a table named dept
, and then alters the dept
table to define and enable a unique key on the dname
column. The constraint dept_dname_uq
identifies the dname
column as a unique key. The USING_INDEX
clause creates an index on a table dept
with the index statement specified to enable the unique constraint.
The following example creates a table named emp
, and then alters the emp
table to define and enable a primary key on the ename
column. The emp_ename_pk
constraint identifies the column ename
as a primary key of the emp
table. The USING_INDEX
clause creates an index on a table emp
with the index statement specified to enable the primary constraint.
See Also