René Nyffenegger's collection of things on the web
René Nyffenegger on Oracle - Most wanted - Feedback -
 

alter table

Adding Foreign key

ALTER TABLE table_name
  ADD CONSTRAINT constraint_name FOREIGN KEY (col1, col2) 
  REFERENCES table_2 (cola,colb);

Adding a unique constraint

The following example adds a unique constraint on the column column_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 the nologging 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 the nologging option.

Specifying tablespace for index

alter table tbl add constraint pk_tbl
primary key (col_1, col_2)
using index tablespace ts_idx

Removing a constraint

alter table table_name drop constraint constraint_name;