This tutorial page is based on examples to be easier to follow. Statement Oracle Alter Table allows you 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 basic Oracle Alter Table syntax is:
ALTER TABLE <table_name> <changing conditions>;
To go through the following Alter Table examples we would need a table. This Oracle Create Table statement will use a Select statement to create an empty table named CUSTOMERS. The table has only one column named “ID“. The table will be created as an empty one because the examples will modify table properties mainly.
CREATE TABLE customers AS SELECT rownum AS ID FROM dual WHERE 1 = 2;
The first syntax is about how to add a new column to the table. Take a look at the syntax because we will use it in the following example.
ALTER TABLE <table_name>
ADD <column_name> <column_type> [<additional column conditions>];
This example based on the syntax above will add a new column named “SURNAME” to table CUSTOMERS. The column data type is VARCHAR2 and length 500 characters. The following “NOT NULL” constraint is added because every our customer has a surname and with that condition we will set it as mandatory. The “NOT NULL” condition does not have to be added and then the column can be left empty – without any value.
ALTER TABLE customers
ADD surname VARCHAR2(500) NOT NULL;
To add more than one column 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 example will add three new columns named “FORENAME“, “DATE_OF_BIRTH” and “EMAIL“. The upper two columns are mandatory and the “EMAIL” can be left empty. The “FORENAME” and “EMAIL” are text columns 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 this statement you should have 5 columns in the table: “ID“, “SURNAME“, “FORENAME“, “DATE_OF_BIRTH” and “EMAIL“.
The third example is about how to amend your column setting. You can change your column data type or the size amount when it has no data. This statement also allows adding or removing constraints and restrictions. The Oracle Alter Table syntax for MODIFY is following.
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 is.
ALTER TABLE customers MODIFY forename VARCHAR2(200);
The fourth 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 of renaming a column will change “DATE_OF_BIRTH” column to “DOB” and rest of the column settings will remain as they were before.
ALTER TABLE customers RENAME COLUMN date_of_birth TO dob;
Now has left to try to remove a column from the table. The keyword for removing is “DROP COLUMN“. Once the column is removed it will be quite complicated to get it back so be very careful with this statement.
ALTER TABLE <table_name>
DROP COLUMN <column_name>;
The example below will remove the “SURNAME” column from table CUSTOMERS and to do it we are using the Oracle Alter Table statement as all examples above.
ALTER TABLE customers
DROP COLUMN surname;
Besides renaming the table columns you also can rename the table name and the keyword is “RENAME TO“. Renaming tables can be quite useful to do switching and replace existing table with an empty one. The meaning of this action is to empty a big table from the data. The Oracle TRUNCATE TABLE statement can remain slow for a very big table 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 statement all your SQL queries need to be written to 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 as dangerous as DROP COLUMN because you can always rename the table back as it was before chaining the name. It’s important to know that renaming a table will put your existing code into invalid state either you will need to recompile it or do some tailoring.
See Also:
Oracle Select Home