“I want to add a new column to an existing table.”
“I want to change a text column to a numeric type.”
“I want to delete an unnecessary column.”
These requirements frequently arise during database operation or development. In Oracle Database, the ALTER TABLE statement is used for these operations.
This article explains the specific steps for adding, modifying, and dropping columns using Oracle SQL. In particular, it provides a detailed explanation with working samples on how to overcome the common practical hurdle where “changing the type causes an error when data exists.”
What you will learn in this article:
- Basic syntax for adding, modifying, and dropping columns (
ALTER TABLE) - [Important] Safe procedures for changing the data type of a column that contains data
- Troubleshooting common troubles (ORA errors) encountered in practice
- [FAQ] Changing column order and recovery from accidental deletion
Conclusion: Column Operations To-Do List (Basic Syntax)
For those short on time, here is a summary of the basic syntax.
Adding a Column:
ALTER TABLE table_name ADD (column_name data_type [DEFAULT value] [constraint]);
Modifying a Column (Simple length expansion, etc.):
ALTER TABLE table_name MODIFY (column_name new_data_type [DEFAULT new_value]);
Modifying a Column (With data / Type change):
MODIFY cannot be used directly. You need the procedure: “Add New Column → Migrate Data → Drop Old Column” (details below).
Dropping a Column:
ALTER TABLE table_name DROP COLUMN column_name;
Renaming a Column:
ALTER TABLE table_name RENAME COLUMN old_column_name TO new_column_name;
Note:
ALTER TABLEis DDL (Data Definition Language). Execution triggers an immediate auto-commit, and it cannot be undone withROLLBACK.
1. Background and Basics: What is ALTER TABLE?
When there are changes to the Database Design (DD) document, the table structure must be modified. In Oracle Database, the ALTER TABLE statement is used to change the definition of a table.
Quick Note for Beginners
- Impact: Adding a column does not affect existing data (it will contain NULL or a specified default value).
- Locking: During DDL execution, the table is locked momentarily (or until processing completes), preventing changes by other users. Execution during business hours requires caution.
2. Practice: Steps for Adding, Modifying, and Dropping Columns
From here, I will explain using actual SQL. You can try this in your own verification environment.
Prerequisite: Creating a Sample Table
First, create an employee table (employees) for verification.
-- Create sample table
CREATE TABLE employees (
emp_id NUMBER(5) CONSTRAINT pk_emp PRIMARY KEY,
emp_name VARCHAR2(100),
hire_date DATE,
note VARCHAR2(50) -- Used later for type change test
);
-- Insert test data (Using US format name)
INSERT INTO employees (emp_id, emp_name, hire_date, note) VALUES (1001, 'John Doe', SYSDATE, '12345');
COMMIT;
SQL> CREATE TABLE employees (
2 emp_id NUMBER(5) CONSTRAINT pk_emp PRIMARY KEY,
3 emp_name VARCHAR2(100),
4 hire_date DATE,
5 note VARCHAR2(50)
6 );
Table created.
SQL> INSERT INTO employees (emp_id, emp_name, hire_date, note)
2 VALUES (1001, 'John Doe', SYSDATE, '12345');
1 row created.
SQL> COMMIT;
Commit complete.
SQL> SELECT * FROM employees;
EMP_ID EMP_NAME HIRE_DATE NOTE
---------- --------------- --------- --------------------
1001 John Doe 13-DEC-25 12345
Step 1: Adding a Column (ADD)
Add a phone number (phone_number) column.
-- Adding a column
ALTER TABLE employees ADD (phone_number VARCHAR2(15));
SQL> DESC employees
Name Null? Type
----------------------------------------- -------- ----------------------------
EMP_ID NOT NULL NUMBER(5)
EMP_NAME VARCHAR2(100)
HIRE_DATE DATE
NOTE VARCHAR2(50)
SQL> ALTER TABLE employees ADD (phone_number VARCHAR2(15));
Table altered.
SQL> DESC employees
Name Null? Type
----------------------------------------- -------- ----------------------------
EMP_ID NOT NULL NUMBER(5)
EMP_NAME VARCHAR2(100)
HIRE_DATE DATE
NOTE VARCHAR2(50)
PHONE_NUMBER VARCHAR2(15)
To add multiple columns at once, separate them with commas inside the parentheses.
ALTER TABLE employees ADD (
email VARCHAR2(100),
department_id NUMBER(4)
);
SQL> ALTER TABLE employees ADD (
2 email VARCHAR2(100),
3 department_id NUMBER(4)
4 );
Table altered.
SQL> DESC employees
Name Null? Type
----------------------------------------- -------- ----------------------------
EMP_ID NOT NULL NUMBER(5)
EMP_NAME VARCHAR2(100)
HIRE_DATE DATE
NOTE VARCHAR2(50)
PHONE_NUMBER VARCHAR2(15)
EMAIL VARCHAR2(100)
DEPARTMENT_ID NUMBER(4)
Step 2: Modifying a Column (MODIFY) and Handling Errors
There are two patterns for modifying column definitions: “Cases that go smoothly” and “Cases that result in errors.”
Case A: Smooth Case (Expanding Length)
Expand the length of the employee name (emp_name) from 100 bytes to 200 bytes. This is not a problem even if data exists.
-- Expanding column length
ALTER TABLE employees MODIFY (emp_name VARCHAR2(200));
SQL> SELECT emp_id,emp_name,hire_date,note FROM employees;
EMP_ID EMP_NAME HIRE_DATE NOTE
---------- --------------- --------- --------------------
1001 John Doe 13-DEC-25 12345
SQL> ALTER TABLE employees MODIFY (emp_name VARCHAR2(200));
Table altered.
SQL> DESC employees
Name Null? Type
----------------------------------------- -------- ----------------------------
EMP_ID NOT NULL NUMBER(5)
EMP_NAME VARCHAR2(200) ★
HIRE_DATE DATE
NOTE VARCHAR2(50)
PHONE_NUMBER VARCHAR2(15)
EMAIL VARCHAR2(100)
DEPARTMENT_ID NUMBER(4)
Case B: Error Case (Changing Data Type)
What happens if you try to change the note column (currently VARCHAR2) to NUMBER type?
If data (‘12345’) already exists, Oracle returns an error because it cannot guarantee data integrity.
-- Failure Example: Changing type of a column with data
ALTER TABLE employees MODIFY (note NUMBER(10));
-- Result: ORA-01439: column to be modified must be empty to change datatype
SQL> DESC employees
Name Null? Type
----------------------------------------- -------- ----------------------------
EMP_ID NOT NULL NUMBER(5)
EMP_NAME VARCHAR2(200) ★
HIRE_DATE DATE
NOTE VARCHAR2(50)
PHONE_NUMBER VARCHAR2(15)
EMAIL VARCHAR2(100)
DEPARTMENT_ID NUMBER(4)
SQL> SELECT emp_id,emp_name,hire_date,note FROM employees;
EMP_ID EMP_NAME HIRE_DATE NOTE
---------- --------------- --------- --------------------
1001 John Doe 13-DEC-25 12345 ★
SQL> ALTER TABLE employees MODIFY (note NUMBER(10));
ALTER TABLE employees MODIFY (note NUMBER(10))
*
ERROR at line 1:
ORA-01439: column to be modified must be empty to change datatype
Explanation: ORA-01439 means “The column must be empty (NULL) to change the data type.” Oracle does not automatically convert the string ‘12345’ into a number.
[Solution] Correct Procedure for Changing Data Type
To change the type of a column containing data, execute the following 4 steps:
- Add a new column with the desired data type.
- Copy (UPDATE) existing data to the new column while converting it.
- Drop the old column.
- Rename the new column to the original name.
-- 1. Add new column (temporary name) as number type
ALTER TABLE employees ADD (note_new NUMBER(10));
-- 2. Convert and copy data (Convert from character to number)
UPDATE employees SET note_new = TO_NUMBER(note);
COMMIT; -- Commit as appropriate if data volume is large
-- 3. Drop old column
ALTER TABLE employees DROP COLUMN note;
-- 4. Rename new column to original name
ALTER TABLE employees RENAME COLUMN note_new TO note;
With this, you have successfully changed the data type from VARCHAR2 to NUMBER while maintaining the data content.
Step 3: Dropping a Column (DROP COLUMN)
Delete a column that is no longer needed.
-- Dropping a column
ALTER TABLE employees DROP COLUMN phone_number;
Risk: Column deletion physically erases data, so it cannot be undone. Since column deletion on large-scale tables imposes a high system load, consider SET UNUSED described later.
3. Troubleshooting (Common Errors)
Here are errors frequently encountered during column operations and their solutions.
| Error Code | Error Message | Cause | Solution |
| ORA-01439 | column to be modified must be empty to change datatype | Attempted to change type (e.g., Char ⇔ Num) while data exists. | Follow the procedure in “Step 2 Case B” above: Add New → Migrate → Drop Old. |
| ORA-01441 | cannot decrease column length because some value is too large | Attempted to reduce length, but existing data is too long. | Check max data length to decide size, or UPDATE data to be shorter before reducing. |
| ORA-00054 | resource busy and acquire with NOWAIT specified… | Another session is locking the table (e.g., updating). | Wait for that session to end or execute during maintenance hours. |
4. Operational, Monitoring, and Security Considerations
Precautions for Production Environments
- Backup: Always perform a backup (Export/Data Pump) before work. Column deletion and type changes are particularly high-risk operations.
- Execution Timing: Since DDL acquires an exclusive lock, perform it during times with low access traffic.
- Invalid Objects: Changing the table structure may cause views or PL/SQL packages referencing that table to become “INVALID”. Recompilation is required after the work.
5. FAQ: Frequently Asked Questions
Q1. Can I specify the column order (position) when adding a column?
A. Basically, no.
In Oracle Database, newly added columns are always added to the very end. If you want to “add it as the 2nd column,” you need to recreate the table (CREATE TABLE AS SELECT …).
Q2. Can I drop multiple columns at once?
A. Yes, it is possible.
ALTER TABLE employees DROP (email, department_id);
By writing it this way, you can delete multiple columns together.
Q3. Dropping a column takes too long. Is there a faster way?
A. Consider SET UNUSED.
Dropping a column in a table with hundreds of millions of rows is a very heavy process. It is recommended to first logically make the column unusable and perform the physical deletion later.
-- Immediately make inaccessible (Data remains, but invisible to SELECT etc.)
ALTER TABLE employees SET UNUSED (phone_number);
-- Execute physical deletion during maintenance hours, etc.
ALTER TABLE employees DROP UNUSED COLUMNS CHECKPOINT 1000;
6. Summary
The points for table column operations in Oracle Database are as follows:
- Add (ADD): Columns are added to the end of the table.
- Modify (MODIFY):
- Length expansion is easy.
- Type change or length reduction results in an error (
ORA-01439/ORA-01441) if data exists. In this case, handle it with the “Create New → Migrate → Drop Old” procedure.
- Drop (DROP): Data is permanently lost, so prior backup is mandatory.
- Transaction: DDL commits immediately, so rollback is not possible.
I strongly recommend first experiencing the ORA-01439 error in a development environment and practicing the avoidance steps.
This article explains based on Oracle Database 19c (screens and default values may differ in other versions).
コメント