Sayantan's Blog On Oracle

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:

ADDING SINGLE COLUMN:

ALTER TABLE EMP_ALTER_TAB ADD(JOB_ID VARCHAR2(10));

DESC EMP_ALTER_TAB;

ADDING MULTIPLE COLUMNS:

ALTER TABLE EMP_ALTER_TAB ADD(HIRE_DATE      DATE,
                              SALARY         NUMBER(8, 2),
                              DEPARTMENT_ID  NUMBER(4));

DESC EMP_ALTER_TAB;

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

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;

DROP ONE OR MORE EXISTING COLUMNS:

DROP SINGLE COLUMN:

ALTER TABLE EMP_ALTER_TAB DROP(JOB_ID);

DESC EMP_ALTER_TAB;

ALTER TABLE EMP_ALTER_TAB DROP COLUMN LAST_NAME;

DESC EMP_ALTER_TAB;

DROP MULTIPLE COLUMNS:

ALTER TABLE EMP_ALTER_TAB DROP(HIREDATE,
                               SALARY,
                               DEPARTMENT_ID);

DESC EMP_ALTER_TAB;

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

SELECT TABLE_NAME, 
       TABLESPACE_NAME, 
       STATUS, 
       NUM_ROWS, 
       TABLE_LOCK
FROM USER_TABLES
WHERE TABLE_NAME = 'EMP_ALTER_TEST';

RELATED TOPICS:

• CREATE Statement in Oracle