Alter Table | Add or Drop Column | Oracle SQL Tutorials -29

I will explain Alter Table and Alter Table Add or Drop Column in Oracle SQL in this post of Oracle SQL Tutorial series.

Alter Table | Add or Drop Column in Oracle

Read the previous post of this tutorial series before this.

Create Table ( CTAS ) and Data Types, LOB Data Types in Oracle SQL | Oracle SQL Tutorials -27

Alter Table

You can perform the following tasks using the Alter command.

• Rename Table
• Add Column
• Drop Column
• Rename Column
• Disable Constraint
• Enable Constraint
• Add Partition
• Drop Partition
• Read Only
• Read Write

You can add new column as follows.

ALTER TABLE People ADD DateOfBirth date;

You can drop an existing column as follows.

ALTER TABLE People DROP COLUMN DateOfBirth;

You can rename column name as follows using alter command.

 alter table hr.employees rename column employee_id to emp_id;

You can convert table into read-only and read write mode as follows.

alter table hr.employees read only;


SQL> alter table msdeveci.mytest read only;

Table altered.

SQL> alter table msdeveci.mytest read write;

Table altered.

SQL>

You can rename an existing table as follows.

alter table hr.employees rename to myemployee;

You can add new partition to existing table as follows.

alter table hr.employees add partition part values (‘G3_201407_07');

You can add primary key or constraint as follows.

ALTER TABLE HR.EMPLOYEES ADD (CONSTRAINT EMP_EMP_ID_PK PRIMARY KEY (EMP_ID));

You can disable and enable existing contraint as follows.

 ALTER TABLE HR.DEPARTMENTS DISABLE CONSTRAINT DEPT_LOC_FK;


ALTER TABLE HR.DEPARTMENTS ENABLE CONSTRAINT DEPT_LOC_FK;

You can rename an existing table as follows.

SQL> alter table msdeveci.test_table rename to mytest;   

Table altered.

SQL>

You can add new column, drop existing column and modify column as follows.

SQL> alter table msdeveci.mytest add city varchar2(24);

Table altered.

SQL> alter table msdeveci.mytest modify city varchar2(36);

Table altered.



SQL> alter table msdeveci.mytest drop column city;

Table altered.

SQL>

Dou want to learn Oracle SQL Tutorial for Beginners, then read the following articles.

Oracle SQL Tutorials For Beginners – Learn Oracle SQL from scratch with Oracle SQL Online Course