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

Hi,

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

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

 1,342 views last month,  5 views today