Table of Contents
ALTER Table Statement in Oracle
ALTER statement is used to do below operations:
- ADD ONE OR MORE NEW COLUMNS
- MODIFY ONE OR MORE EXISTING COLUMNS
- SET A DEFAULT VALUE FOR A COLUMN
- REMOVE A DEFAULT VALUE FOR A COLUMN
- RENAME AN EXISTING COLUMN
- DROP ONE OR MORE EXISTING COLUMNS
- RENAME AN EXISTING TABLE
ADD ONE OR MORE NEW COLUMNS:
We have a table called EMP_ALTER_TAB with below definition:
![](https://sayantanblogonoracle.in/wp-content/uploads/2022/08/image-210.png)
ADDING SINGLE COLUMN:
ALTER TABLE EMP_ALTER_TAB ADD(JOB_ID VARCHAR2(10));
DESC EMP_ALTER_TAB;
![ALTER Table Statement in Oracle : output](https://sayantanblogonoracle.in/wp-content/uploads/2022/08/image-211.png)
ADDING MULTIPLE COLUMNS:
ALTER TABLE EMP_ALTER_TAB ADD(HIRE_DATE DATE, SALARY NUMBER(8, 2), DEPARTMENT_ID NUMBER(4));
DESC EMP_ALTER_TAB;
![](https://sayantanblogonoracle.in/wp-content/uploads/2022/08/image-212.png)
MODIFY ONE OR MORE EXISTING COLUMNS:
MODIFY SINGLE COLUMN:
We will increase the JOB_ID column's length to 15 from 10.
ALTER TABLE EMP_ALTER_TAB MODIFY(JOB_ID VARCHAR2(15));
DESC EMP_ALTER_TAB;
![](https://sayantanblogonoracle.in/wp-content/uploads/2022/08/image-213.png)
MODIFY MULTIPLE COLUMNS:
ALTER TABLE EMP_ALTER_TAB MODIFY(FIRST_NAME VARCHAR2(40), SALARY NUMBER(10, 2), DEPARTMENT_ID NUMBER(6));
DESC EMP_ALTER_TAB;
![](https://sayantanblogonoracle.in/wp-content/uploads/2022/08/image-214.png)
SET A DEFAULT VALUE FOR A COLUMN:
SINGLE COLUMN DEFAULT VALUE:
ALTER TABLE EMP_ALTER_TAB MODIFY(HIRE_DATE DATE DEFAULT SYSDATE);
Checking the data dictionary for default value:
SELECT TABLE_NAME, COLUMN_NAME, DATA_TYPE, DATA_LENGTH, DATA_DEFAULT FROM USER_TAB_COLUMNS WHERE TABLE_NAME = 'EMP_ALTER_TAB';
![](https://sayantanblogonoracle.in/wp-content/uploads/2022/08/image-215.png)
MULTIPLE COLUMN DEFAULT VALUE:
ALTER TABLE EMP_ALTER_TAB MODIFY(HIRE_DATE DATE DEFAULT SYSDATE, SALARY NUMBER(10, 2) DEFAULT 1000);
SELECT TABLE_NAME, COLUMN_NAME, DATA_TYPE, DATA_LENGTH, DATA_DEFAULT FROM USER_TAB_COLUMNS WHERE TABLE_NAME = 'EMP_ALTER_TAB';
![](https://sayantanblogonoracle.in/wp-content/uploads/2022/08/image-217.png)
REMOVE A DEFAULT VALUE FOR A COLUMN:
ALTER TABLE EMP_ALTER_TAB MODIFY(HIRE_DATE DATE DEFAULT NULL, SALARY NUMBER(10, 2) DEFAULT NULL);
SELECT TABLE_NAME, COLUMN_NAME, DATA_TYPE, DATA_LENGTH, DATA_DEFAULT FROM USER_TAB_COLUMNS WHERE TABLE_NAME = 'EMP_ALTER_TAB';
![](https://sayantanblogonoracle.in/wp-content/uploads/2022/08/image-224.png)
RENAME AN EXISTING COLUMN:
Renaming the HIRE_DATE column to HIREDATE.
ALTER TABLE EMP_ALTER_TAB RENAME COLUMN HIRE_DATE TO HIREDATE;
DESC EMP_ALTER_TAB;
![](https://sayantanblogonoracle.in/wp-content/uploads/2022/08/image-218.png)
DROP ONE OR MORE EXISTING COLUMNS:
DROP SINGLE COLUMN:
ALTER TABLE EMP_ALTER_TAB DROP(JOB_ID);
DESC EMP_ALTER_TAB;
![](https://sayantanblogonoracle.in/wp-content/uploads/2022/08/image-219.png)
ALTER TABLE EMP_ALTER_TAB DROP COLUMN LAST_NAME;
DESC EMP_ALTER_TAB;
![](https://sayantanblogonoracle.in/wp-content/uploads/2022/08/image-220.png)
DROP MULTIPLE COLUMNS:
ALTER TABLE EMP_ALTER_TAB DROP(HIREDATE, SALARY, DEPARTMENT_ID);
DESC EMP_ALTER_TAB;
![](https://sayantanblogonoracle.in/wp-content/uploads/2022/08/image-221.png)
RENAME AN EXISTING TABLE:
ALTER TABLE EMP_ALTER_TAB RENAME TO EMP_ALTER_TEST;
SELECT TABLE_NAME, TABLESPACE_NAME, STATUS, NUM_ROWS, TABLE_LOCK FROM USER_TABLES WHERE TABLE_NAME = 'EMP_ALTER_TAB';
![](https://sayantanblogonoracle.in/wp-content/uploads/2022/08/image-222.png)
SELECT TABLE_NAME, TABLESPACE_NAME, STATUS, NUM_ROWS, TABLE_LOCK FROM USER_TABLES WHERE TABLE_NAME = 'EMP_ALTER_TEST';
![](https://sayantanblogonoracle.in/wp-content/uploads/2022/08/image-223.png)
RELATED TOPICS: