Alter Table Examples.

How to add a constraint to table

ALTER TABLE table_name   ADD CONSTRAINT constraint_name FOREIGN KEY (col_1, col_2)    REFERENCES table_2 (cola,colb);
Removing a constraint
alter table table_name drop constraint constraint_name;
Adding a unique constraint
The following example adds a unique constraint on the columncolumn_name in the table whose name is table_name. The name of the constraint is constraint_name.
alter table table_name   add constraint constraint_name   unique (column_name) 
Disabling constraints
Integrity constraints can be disabled with the alter table command.
alter table table-name disable constraint-specification; alter table table-name disable constraint constraint-name; 
Adding a new Column
alter table foo_table add bar_column char(1); 
Modifying a column
Renaming a column name
alter table some_table rename column column_name to new_column_name; 
Changing a column's type
A column's type can be changed if the column is empty within the entire table, otherwise Oracle gives an ORA-01440: column to be modified must be empty to decrease precision or scale.
alter table some_table modify (column_name number); 
Changing null to not null or vice versa
alter table some_table modify (column_name not null); 
alter table some_table modify col_not_null number null; 
The parenthesis after the modify are optional.
alter table ... move
Alter table ... move partition
This is one of the few statements that can make use of thenologging option.
Alter table .. rename to ...
alter table old_table_name rename to new_table_name; 
This is equivalent to rename old_table_name to new_table_name.
Alter table ... split partition
This is one of the few statements that can make use of thenologging option.
Specifying tablespace for index
alter table tbl add constraint pk_tbl primary key (col_1, col_2) using index tablespace ts_idx