In this Oracle tutorial, we will cover the use of Alter table add column in Oracle. Here we will learn how to perform alter table columns in the oracle database. Also, we will cover these topics.
- Alter table add column in oracle
- Alter table add column oracle default value
- Alter table add column oracle default value null
- Alter table add column oracle varchar2
- Alter table add column oracle nullable
- Alter table add column oracle not null
- Alter table add column oracle number
- Alter table add column oracle after
- Alter table add column oracle date
- Alter table add column oracle default value timestamp
Note: All the dataset used in this tutorial is from Kaggle and we do not guarantee accuracy. The dataset is used to explain the concepts well.
Alter table add column in oracle
In this section, we will learn how to add column(s) in the oracle database using alter table command.
- In Oracle,
ALTER TABLE
statement is used to add, modify, delete or drop columns from the table. - Alter table statement is also used for changing the table name in oracle database.
- Here is the syntax for using Alter table command in oracle database.
ALTER TABLE <table_name>
ADD <column_name> <data-type> <other-definiton>;
Example:
In our example, we have a table: life_expectancy within the schema: c##sqlserverguides. We will add a new column: Population using Alter table statement.
COUNTRY_NAME YEAR LIFEEXPECTANCY
-------------------- ---------- --------------
Australia 2016 82.519997
Canada 2016 81.849998
Germany 2016 80.93
Mexico 2016 76.379997
United Kingdom 2016 80.900002
United States 2016 78.860001
Source Code:
Here is the source code of an example in which we are going to create a new column for the life_expectancy table created inside the user c##sqlserverguides.
Alter table c##sqlserverguides.life_expectancy
Add Population NUMBER
commit
Implementation on SQL developer tool

Output:
This output is divided into two parts, in the first part, we have displayed the original table while in the second parted we have added a new column ‘Population’ using the alter table statement.

Also, read: How to Check Oracle Database Version
Alter Table Add Column Oracle Default Value
In this section, we will learn how to perform alter table add column oracle default value.
- We will learn how to give a default value to the column in oracle database using alter table statement.
- Suppose, you already have a table now you are adding a new column to it. If you won’t assign a default value to it then it will have empty space.
- In our previous section, we have added a column ‘Popluation’ and we didn’t provided any default value to it. As a result of which the entire column was empty.
- In our example, we will add a new column Population with the default value as 100000.
Example:
In our example, we will use the below table and add a new column Population with a default value of 100000.
COUNTRY_NAME YEAR LIFEEXPECTANCY
-------------------- ---------- --------------
Australia 2016 82.519997
Canada 2016 81.849998
Germany 2016 80.93
Mexico 2016 76.379997
United Kingdom 2016 80.900002
United States 2016 78.860001
Source Code:
Here is the source code of an example in which we are going to create a new column for the life_expectancy table created inside the user c##sqlserverguides. The new column’s name is Population and it has a default value of 100000.
alter table c##sqlserverguides.life_expectancy
Add population Number default 100000;
COMMIT
Implementation on SQL developer tool

Output:
This output is divided into two parts, in the first part we have not used the default value due to which the new column has an empty value. Whereas in the second part default value is assigned to a new column population.

Read: How to Get List all Tables in Oracle Database
Alter table add column oracle default value null
In the previous section, we have discussed the default values in alter table. In this section, we will perform alter table add column oracle default value null.
- Null in oracle database refers to nothing or an empty value.
- Using alter table command we can add a new column and at the end of that column we can mention the default value as null.
- Oracle automatically sets the default value to null incase user has not specified any.
Example:
In our example, we will use the below table and add a new column Population with a default value that is null.
COUNTRY_NAME YEAR LIFEEXPECTANCY
-------------------- ---------- --------------
Australia 2016 82.519997
Canada 2016 81.849998
Germany 2016 80.93
Mexico 2016 76.379997
United Kingdom 2016 80.900002
United States 2016 78.860001
Source Code:
Here is the source code of an example in which we are going to create a new column for the life_expectancy table created inside the user c##sqlserverguides. The new column’s name is Population and it has a default value that is null.
alter table c##sqlserverguides.life_expectancy
Add population Number default null;
COMMIT
Implementation o SQL developer tool

Output:
In this output, since we set the default value as null that is why the result shown is empty.

Read: Connect Excel to Oracle Database
Alter table add column oracle varchar2
In this section, we will learn to implement alter table add column oracle varcahr2.
- We will add a new column with the name status in life_expectancy table. This column will have data type as varchar2 & will tell whether the country is developer or developing.
- Varchar2 is a data type in oracle used to store strings and character(s) in oracle database.
- While implementing varchar2 data type you may get the following error:
ERROR at line 2:
ORA-00906: missing left parenthesis
00906. 00000 - "missing left parenthesis"
- This error can be fixed by adding a size or limitation to the varchar2. Use
varchar2(20)
, here 20 is the imitation set for varchar data type.
Example:
In our example, we will demonstrate how to alter the table add a column with varchar2 data type in the oracle database. In the below code, c##sqlserverguides in the schema name, and life_expectancy is the table name.
Alter table c##sqlserverguides.life_expectancy
Add status VARCHAR2(20) default 'Developed';
COMMIT
Implementation on SQL Developer tool

Output:
In this output, a new column ‘Status’ is added and it has varchar2 datatype.

Read: How to Fetch Data from Oracle Database in Python
Alter table add column oracle nullable
In this section, we will learn to alter the table add column oracle nullable.
- While creating a table we have added a constraint not null. Which means that columns can’t be left empty.
- Suppose the business requirement changed in future not that column can be left empty. That thing we are going to learn in this section.
- Using Alter table keyword we can change the constraint type of a column(s).
Example:
In our example, we are going to change the constraint type for the YEAR column from Not Null to Null. This means a user can now leave the column empty. Below is the table description that we are going to use.
Name Null? Type
----------------------------------------- -------- -------------------
COUNTRY_NAME NOT NULL VARCHAR2(20)
YEAR NOT NULL NUMBER(38)
LIFEEXPECTANCY NOT NULL FLOAT(126)
STATUS VARCHAR2(20)
We tried passing the null or empty value in the column with not null constraint. And it threw this error:
One error saving changes to table "C##SQLSERVERGUIDES"."LIFE_EXPECTANCY":
Row 7: ORA-01400: cannot insert NULL into ("C##SQLSERVERGUIDES"."LIFE_EXPECTANCY"."YEAR")
ORA-06512: at line 1

Moving forward we will convert the YEAR column to nullable using alter table statement in the oracle database.
Source Code:
Here is the source code, to convert the oracle database column constraint to Nullable.
Alter table c##sqlserverguides.life_expectancy
MODIFY (YEAR NULL);
COMMIT
Implementation on SQL developer tool

Output:
In this output, we are going to perform the same activity that we did at the start of the example. We will try to put the null value inside the YEAR column.

Read: Connect to Oracle Database using Python
Alter table add column oracle not null
In this section, we will learn about alter table add column oracle not null.
- Not Null is the constraint in oracle datbase that means the that column can’t be left empty.
- Not Null constraint is applied on the columns which are mandatory to fill. for example, name, mobile number, email, etc are some of the important columns.
- If these columns are left empty then it will have a great impact on the database.
- Any column with Not Null constraint if left empty will throw an error.
- Using Alter table statement we can modify the column constraints. Incase you are getting below error while changing the constraints that means there is a null value in the record of that column. Remove that null value and then try again.
Error report -
ORA-02296: cannot enable (C##SQLSERVERGUIDES.) - null values found
02296. 00000 - "cannot enable (%s.%s) - null values found"
*Cause: an alter table enable constraint failed because the table
contains values that do not satisfy the constraint.
*Action: Obvious
- In our example, we will show you convert nullable column to not null in oracle database. you can see in the below image that column Year currently don’t have not null constraint.

Source Code:
In this source code, we have changed the constraint of the Year column from nullable to not null using alter table in the oracle database.
Alter table c##sqlserverguides.life_expectancy
modify (YEAR Not Null);
COMMIT
Here is the implementation on the SQL developer tool

Output:
This output is divided into two parts, the first part shows that there is no constraint applied on the Year column whereas in the second picture we have applied the Not Null constraint using Alter table statement.

Read: Number Datatype in Oracle Database
Alter table add column oracle number
In this section, we will learn how to perform alter table add column oracle number in oracle database.
- We will learn to add new column with data type as Number using Alter Table in oracle database.
- In our example, we will add new column as AdultMortality which will be the probability of dying between 15 and 60 years per 1000 population.
Source code:
In this source code, we have added a new column with the name adultMortality with Number data type.
Alter table c##sqlserverguides.life_expectancy
Add adultMortality NUMBER;
COMMIT
Here is the implementation on the SQL developer tool

Output:
This output shows that the before database doesn’t have an AdultMortality column. But we have added it using Alter table statement in the oracle database.

Read: Oracle Database vs MySQL Database
Alter table add column oracle after
In this section, we will learn about alter the table column oracle after.
- We can add new column in a table using alter table command in oracle database not only that we can also control the position where we want to place the new column.
- If you want to add a new column: ‘Last Name’ then probably you want to add it after the ‘First Name’ column.
- So in this section, we will learn how to add a new column after a specific column.
- In our example, we are following life expectancy table and so far we have this data in our life_expectancy table.
COUNTRY_NAME YEAR LIFEEXPECTANCY ADULTMORTALITY
-------------------- ---------- -------------- --------------
Australia 2016 82.519997 59
Canada 2016 81.849998 64
Germany 2016 80.93 68
Mexico 2016 76.379997 122
United Kingdom 2016 80.900002 69
United States 2016 78.860001 13
- Moving forward, we are going to add a new column after LifeExpectancy column using alter table statement in oracle database.
Source Code:
In this source code, we have used the after keyword in Alter table to place the new column at the specific position in the table.
Alter table c##sqlserverguides.life_expectancy
Add status NUMBER After LIFEEXPECTANCY;
COMMIT;
Implementation on SQL developer tool

Output:
In this output, the date column has been added using alter table statement. Since the date column was assigned with the default value as sysdate so it has picked up the system date and published it on the database.
Read: How to create a database in Oracle 19c
Alter table add column oracle date
In this section, we will learn about Alter table add column oracle date in the oracle database.
- We will create a column with date data type in oracle database.
- Using Date data type in oracle database we can insert the record with date. This helps in adding detail to the database only makes easy to fetch data.
- Date can be added to an existing table using alter table comand in oracle database.
- Use sysdate as default value while creating date column. This will automatically assign the current date of the system.
Source Code:
In this source code, using the alter table statement we have added a new column Log_date. This column has default value as sysdate, which means every time the database is being created it will pick up the system date automatically.
ALTER table c##sqlserverguides.life_expectancy
Add Log_date Date default sysdate + 1;
COMMIT;
Implementation on SQL developer tool.

Output:
In this output, the date column has been added using alter table statement. Since the date column was assigned with the default value as sysdate so it has picked up the system date and published it on the database.

Read: Oracle to_char Date Format
Alter table add column oracle default value timestamp
In this section, we will learn about alter table add column oracle default value timestamp.
- We will create a new column in the existing table and will add the timestamp on it.
- Using Alter Table statement in oracle database we can create a new column and for timestamp there are two available options:
- TIMESTAMP WITH TIME ZONE
- TIMESTAMP WITH LOCAL TIME ZONE
- TIMESTAMP WITH TIME ZONE will display the date, time and time zone. All the inforamtion will be picked from the system.
- TIMESTAMP WITH LOCAL TIME ZONE will display date and time only. All the information will be fetched from the system.
- To setup the default time stamp use CURRENT_TIMESTAMP as the default. This will fill the timestamp information instead of null.
Source Code:
In this source code, we have created a Local_Log_Time column to demonstrate TIMESTAMP WITH LOCAL TIME ZONE and Log_time column to demonstrate the TIMESTAMP WITH TIME ZONE in the oracle database.
# time zone
ALTER table c##sqlserverguides.life_expectancy
Add Local_Log_Time TIMESTAMP WITH TIME ZONE DEFAULT Current_Timestamp;
COMMIT;
# Local time zone
ALTER table c##sqlserverguides.life_expectancy
Add Local_Log_Time TIMESTAMP WITH LOCAL TIME ZONE DEFAULT Current_Timestamp;
COMMIT;
Output:
In this output, we have added a new column with the name TimeStamp Log. This will keep a record of the current timestamp in the database. Also, since current_timestamp is set to default so instead of displaying null it will show the current date and time.

You may also like to read the following Oracle tutorials.
In this tutorial, we have learned how to perform alter table columns in the oracle database. Also, we will cover these topics.
- Alter table add column in oracle
- Alter table add column oracle default value
- Alter table add column oracle default value null
- Alter table add column oracle varchar2
- Alter table add column oracle nullable
- Alter table add column oracle not null
- Alter table add column oracle number
- Alter table add column oracle after
- Alter table add column oracle date
- Alter table add column oracle default value timestamp
I am Bijay having more than 15 years of experience in the Software Industry. During this time, I have worked on MariaDB and used it in a lot of projects. Most of our readers are from the United States, Canada, United Kingdom, Australia, New Zealand, etc.
Want to learn MariaDB? Check out all the articles and tutorials that I wrote on MariaDB. Also, I am a Microsoft MVP.