DDL COMMAND :ALTER

Alter command is used to modify the structure of the table.
Using this command we can perform four different operations.
This command contains 4 sub commands:

1. ALTER-MODIFY
2. ALTER-ADD
3. ALTER-RENAME
4. ALTER-DROP

1.ALTER-MODIFY: This command is used to increase/decrease data type & also  we can change                                           the data type from old datatype to new datatype.
SYNTAX: alter table <table_name> modify column_name datatype(size);
SYNTAX TO MODIFY MORE THAN ONE COLUMN:
alter table <table_name> modify(column1 datatype(size),
                                                      column2 datatype(size),
                                                      .
                                                      .
                                                      columnn datatype(size));


2.ALTER-ADD: This command is used to add one or more than one column to the existing                                             table.
SYNTAX: alter table <table_name>  add column datatype(size);
SYNTAX TO ADD ADD MORE THAN ONE COLUMN: 
alter table  <table-name>  add(column1   datatype(size),
                                                 column2 datatype(size),
                                                                   .
                                                                   .
                                                 columnn datatype(size));
NOTE: We cannot add a new column at required position in the table.
              All the columns will be added at the end of the table only.

3.ALTER-RENAME: This command is used to change the column name from old to new column                                           name.
SYNTAX:alter table  <table_name>  rename <old_column_name>  to  <new_column_name>;
NOTE: It is not possible to change more than one column at a time.

SYNTAX TO CHANGE THE TABLE_NAME: 
          alter table  <old_table_name>  RENAME to <new_table_name>;

4.ALTER-DROP: This command is used to drop or remove the existing table.
                                
DROPPING COLUMNS: Only with the release of Oracle 8i has it been possible to drop a column from a table prior to this it was necessary to drop the entire table and rebuild it. Now you can mark a column as unused(logical delete) or delete it completely(physical delete).
           1.Logical delete
           2.Physical delete
1.logical delete:On large tables the process of the physically removing a column can be very time & resource consuming. For this reason you may decide to logically delete it.
SINGLE COLUMN DELETE: 
           alter table <table_name> set unused column <column_name>;
SYNTAX TO DELETE MORE THAN ONE COLUMN:
           alter table <table_name> set unused (column1,column2,column3....columnn);
2.physical delete:
SYNTAX TO DROP A SINGLE COLUMN:
           alter table <table_name> drop column_name;  
SYNTAX TO DROP MORE THAN ONE COLUMN:
           alter table <table_name> drop(list of columns);
SYNTAX TO DROP A TABLE:
           drop table  <table_name>