How To Use ALTER TABLE In Oracle

This help is based on examples so it would be easier to understand. Oracle Alter Table statement allows to amend Oracle table properties. The Alter Table in Oracle does change the table parameters, table partitions or sub-partitions. Also you’ll be able to modify table columns, column size or type. The syntax of Oracle Alter Table is following:

ALTER TABLE <table_name> <changing conditions>;

To try the Alter Table statements we will need a table and using the following Oracle Create Table statement we will have an empty table named CUSTOMERS. This statement is using a Select query to create a new table and it has only one column named “ID“. The table will be created empty because through the examples we will mostly modify table properties and not data.

CREATE TABLE customers AS
 SELECT rownum AS ID
   FROM dual
  WHERE 1 = 2;

The first Oracle Alter Table syntax is about how to create a new column to the table. Please take a look at the syntax because it will used in some examples later.

ALTER TABLE <table_name>
      ADD <column_name> <column_type> [<additional column conditions>];

Now comes the script itself based on the syntax above and it will create a new column named “SURNAME” to table “CUSTOMERS“. The column data type is VARCHAR2 and the length is up to 500 characters. After the VARCHAR2 keyword comes constraint “NOT NULL” and this is added because every our customer has a surname and with that condition we will set the column value to be mandatory. The “NOT NULL” condition does not have to be added and then the column can be left empty or without any value.

ALTER TABLE customers
      ADD surname VARCHAR2(500) NOT NULL;

To add many columns the syntax looks almost the same, only there will be more columns in one statement.

ALTER TABLE <table_name>
  ADD ( <column_name_1> <column_type> [<additional column conditions>],
        <column_name_2> <column_type> [<additional column conditions>],
        ...
        <column_name_n> <column_type> [<additional column conditions>]);

The next Oracle Alter Table example will add three new columns named “FORENAME“, “DATE_OF_BIRTH” and “EMAIL“. The upper two columns are mandatory and the “EMAIL” column can be left empty. The “FORENAME” and “EMAIL” are text columns as VARCHAR2 and the “DATE_OF_BIRTH” is a date type (“DATE“).

ALTER TABLE customers
      ADD ( forename      VARCHAR2(500) NOT NULL,
            date_of_birth DATE          NOT NULL,
            email         VARCHAR2(500)           );

After executing the statement above you should have in the table five columns: “ID“, “SURNAME“, “FORENAME“, “DATE_OF_BIRTH” and “EMAIL“.

The third example is about amending your column setting. You can change the column data type when it is empty or size amount to smaller. Also you can add or remove constraints and restrictions. The syntax of Oracle Alter Table to MODIFY a column’s settings is:

ALTER TABLE <table_name>
      MODIFY <column_name> [<column_type>] [<additional column conditions>];

In this example we will reduce the size of column “FORENAME” from existing 500 to 200 byte. Rest of the columns will remain as they were before.

ALTER TABLE customers
  MODIFY forename VARCHAR2(200);

The fourth Oracle Alter Table statement will rename a column name and the syntax is below.

ALTER TABLE <table_name>
  RENAME COLUMN <column_name> TO <new_column_name>;

The example to rename a column name will change “DATE_OF_BIRTH” column to “DOB” and rest of the column’s settings will remain as is.

ALTER TABLE customers
  RENAME COLUMN date_of_birth TO dob;

Now we will try to remove a column from the table. The keyword to remove is “DROP COLUMN“. Once the column is removed it will be quite complicated to get it back so be very careful with the following statement.

ALTER TABLE <table_name>
      DROP COLUMN <column_name>;

On this example we will remove the “SURNAME” column from table CUSTOMERS and to do it we are using the Oracle Alter Table statement as through all the examples above.

ALTER TABLE customers
      DROP COLUMN surname;

Beside renaming the table columns you also can rename the table name and the keyword for that is “RENAME TO“. Renaming tables can be quite useful to do switching and replacing the existing table with an empty one. The meaning of doing this is to empty a big table from the data. The Oracle TRUNCATE TABLE statement can remain slow for a very big tables and the fastest way to empty is replacing it with an empty table and dropping the existing one.

ALTER TABLE <table_name>
  RENAME TO <new_table_name>;

The following statement will rename table CUSTOMERS to CLIENTS. After executing this all your SQL queries would need to be re-written to use table CLIENTS.

ALTER TABLE customers
  RENAME TO clients;

To rename a table will not erase the data. It only renames the table name and this statement is not so dangerous as DROP COLUMN because you can always rename the table name back as it was before chaining it. Also it’s important to know that renaming a table will put your existing code into invalid state and you will need to recompile it or do some amendments.

See Also:
Oracle Select Oracle Create Table Oracle Table Partition Oracle Home