SQL Alter Table

Alter Table

Alter Table Command :-By alter table command we can alter the structure/Definition of table 

Alter table Operations :- Following operation we can perform by alter table command
                 

                 1) add   
                    a) column   b) constraint    c) partition

                 2) modify 
                    a) datatype of column     b) size of datatype

                 3) enable/disable 
                    a) constraint   b) trigger

                 4) rename  
                    a) column     b) constraint     c) partition

                 5) drop 
                    a) column      b) constraint      c) trigger   d) partition

Examples:-                   
Add column/columns :-
 

SQL>alter table emp2 add description varchar2(20);
SQL>alter table emp2 add (X varchar2(20),Y varchar2(20));

Add constraint :-
 

SQL>alter table emp1 add constraint ck_sal check (sal<10000);
SQL>alter table emp2 add constraint pk_empno primary key (empno);

Modify datatypes:- (Only when data is not present in column)
SQL>alter table emp2 modify(x number,y date);

Modify the size of datatypes:- (there is no constraint to increase the size but we can de-crease the size up to max size data avaailable in the column )

SQL>alter table emp2 modify(x varchar2(30),y varchar2(30));

Disable constraint:-
 

SQL>select * from user_constraints where table_name='EMP2';
SQL>alter table emp2 disable constraint CK_JOB;

Enable constraint :-
 

SQL>alter table emp2 enable constraint CK_JOB;

Rename the column :-
 

SQL>alter table emp2 rename column x to x1;

Rename the constraint :-
 

SQL>alter table emp2 rename constraint CK_JOB to CK_JOB1;

drop a column :-
 

SQL>alter table emp2 drop column x1;

Drop a constraint:-
 

SQL>alter table emp1 drop constraint CK_SAL;

drop a partition :-
 

SQL>alter table sales drop partition p_1997;

Here "sales" is a partition Table and "p_1997" is a name of partition.