ALTER TABLE

The ALTER TABLE statement allows you to rename an existing table. It can also be used to add, modify, or drop a column from an existing table.

Renaming a table
The basic syntax for renaming a table is:
    ALTER TABLE table_name
     RENAME TO new_table_name;

For example:
    ALTER TABLE suppliers
     RENAME TO vendors;
This will rename the suppliers table to vendors.

Adding column(s) to a table
Syntax #1
To add a column to an existing table, the ALTER TABLE syntax is:
    ALTER TABLE table_name
     ADD column_name column-definition;

For example:
    ALTER TABLE supplier
     ADD supplier_name  varchar2(50);

This will add a column called supplier_name to the supplier table.

Syntax #2
To add multiple columns to an existing table, the ALTER TABLE syntax is:
    ALTER TABLE table_name
    ADD (column_1     column-definition,
          column_2     column-definition,
          ...    
          column_n     column_definition );

Drop column(s) in a table
Syntax #1
To drop a column in an existing table, the ALTER TABLE syntax is:
    ALTER TABLE table_name
     DROP COLUMN column_name;
For example:
    ALTER TABLE supplier
     DROP COLUMN supplier_name;

Modifying column(s)(datatypes) in a table

Syntax #1
To modify a column in an existing table, the ALTER TABLE syntax is:
    ALTER TABLE table_name
     MODIFY column_name column_type;


For example:
    ALTER TABLE supplier
     MODIFY supplier_name   varchar2(100)     not null;

This will modify the column called supplier_name to be a data type of varchar2(100) and force the column to not allow null values.

Syntax #2
To modify multiple columns in an existing table, the ALTER TABLE syntax is:
    ALTER TABLE table_name
    MODIFY (     column_1     column_type,
          column_2     column_type,
          ...    
          column_n     column_type );

Rename column(s) in a table
(NEW in Oracle 9i Release 2)
Syntax #1
Starting in Oracle 9i Release 2, you can now rename a column.
To rename a column in an existing table, the ALTER TABLE syntax is:
    ALTER TABLE table_name
     RENAME COLUMN old_name to new_name;


Oracle Applications Fusion Cloud - Inventory

Oracle Cloud/Fusion Manufacturing Cloud training will help you develop the fundamental skills required to set up and use the Manufacturing module. This training covers all the tasks, setups, forms and reports used in Manufacturing and related modules