Sayantan's Blog On Oracle

MODIFY Table Statement in Oracle

MODIFY statement is used to do below operations:

• MODIFY ONE OR MORE EXISTING COLUMNS
• SET A DEFAULT VALUE FOR A COLUMN
• REMOVE A DEFAULT VALUE FOR A COLUMN

We have a table called EMP_ALTER_TAB with below definition:

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;

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;

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';

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';

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';

RELATED TOPICS:

• CREATE Statement in Oracle
• ALTER Table Statement in Oracle
• DROP Table Statement in Oracle