ALTER TABLE v17
Name
ALTER TABLE — Change the definition of a table.
Synopsis
ALTER TABLE <name> action [, ...] ALTER TABLE <name> RENAME COLUMN <column> TO <new_column> ALTER TABLE <name> RENAME TO <new_name> ALTER TABLE <name> { NOPARALLEL | PARALLEL [ <integer> ] }
action is one of:
ADD <column type> [ <column_constraint> [ ... ] ] DROP COLUMN <column> ADD <table_constraint> DROP CONSTRAINT <constraint_name> [ CASCADE ]
Description
ALTER TABLE changes the definition of an existing table. There are several subforms:
ADD column typeThis form adds a column to the table using the same syntax as
CREATE TABLE.DROP COLUMNThis form drops a column from a table. Indexes and table constraints involving the column are dropped as well.
ADD table_constraintThis form adds a constraint to a table. For details, see CREATE TABLE.
DROP CONSTRAINTThis form drops constraints on a table. Currently, constraints on tables don't need unique names, so there might be more than one constraint matching the specified name. All matching constraints are dropped.
RENAME
The RENAME forms change the name of a table (or an index, sequence, or view) or the name of a 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 shows 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're matched with a table owner owning a trigger.
You can use the 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 uses 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 requires rewriting the entire table. This can take a long time for a large table, and it temporarily requires 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 doesn't physically remove the column but makes it invisible to SQL operations. Subsequent insert and update operations in the table store a null value for the column. Thus, dropping a column is quick, but it doesn't immediately reduce the on-disk size of your table since the space occupied by the dropped column isn't reclaimed. The space is reclaimed over time as existing rows are updated.
Changing any part of a system catalog table isn't permitted. Refer to CREATE TABLE for a further description of valid parameters.
Examples
To add a column of type VARCHAR2 to a table:
ALTER TABLE emp ADD address VARCHAR2(30);
To drop a column from a table:
ALTER TABLE emp DROP COLUMN address;
To rename an existing column:
ALTER TABLE emp RENAME COLUMN address TO city;
To rename an existing table:
ALTER TABLE emp RENAME TO employee;
To add a check constraint to a table:
ALTER TABLE emp ADD CONSTRAINT sal_chk CHECK (sal > 500);
To remove a check constraint from a table:
ALTER TABLE emp DROP CONSTRAINT sal_chk;
To reset the degree of parallelism to 0 on the emp table:
ALTER TABLE emp NOPARALLEL;
This 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.
CREATE TABLE dept ( deptno NUMBER(2), dname VARCHAR2(14), loc VARCHAR2(13) );
ALTER TABLE dept ADD CONSTRAINT dept_dname_uq UNIQUE(dname) USING INDEX (CREATE UNIQUE INDEX idx_dept_dname_uq ON dept (dname));
This 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.
CREATE TABLE emp ( empno NUMBER(4) NOT NULL, ename VARCHAR2(10), job VARCHAR2(9), sal NUMBER(7,2), deptno NUMBER(2) );
ALTER TABLE emp ADD CONSTRAINT emp_ename_pk PRIMARY KEY (ename) USING INDEX (CREATE INDEX idx_emp_ename_pk ON emp (ename));