Oracle Alter Table Add Column

I will explain Oracle Alter Table Add Column in this post.

 

Alter Table Add Column in Oracle

Mostly we are using the add column to the existing table. To add new column to the existing table, you should use the alter table add column command as follows.

 

ALTER TABLE table_name
  ADD (column_1 column_definition,
       column_2 column_definition,
       column_3 column_definition,
       ...
       column_n column_definition);



You can add new column as follows.

ALTER TABLE People ADD DateOfBirth date;

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

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

Table altered.

ALTER TABLE msdeveci.mytest ADD (name varchar2(30), city varchar2(50) DEFAULT 'Istanbul');




 

 

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 hr.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

 3,400 views last month,  2 views today

About Mehmet Salih Deveci

I am Founder of SysDBASoft IT and IT Tutorial and Certified Expert about Oracle & SQL Server database, Goldengate, Exadata Machine, Oracle Database Appliance administrator with 10+years experience.I have OCA, OCP, OCE RAC Expert Certificates I have worked 100+ Banking, Insurance, Finance, Telco and etc. clients as a Consultant, Insource or Outsource.I have done 200+ Operations in this clients such as Exadata Installation & PoC & Migration & Upgrade, Oracle & SQL Server Database Upgrade, Oracle RAC Installation, SQL Server AlwaysOn Installation, Database Migration, Disaster Recovery, Backup Restore, Performance Tuning, Periodic Healthchecks.I have done 2000+ Table replication with Goldengate or SQL Server Replication tool for DWH Databases in many clients.If you need Oracle DBA, SQL Server DBA, APPS DBA,  Exadata, Goldengate, EBS Consultancy and Training you can send my email adress [email protected].-                                                                                                                                                                                                                                                 -Oracle DBA, SQL Server DBA, APPS DBA,  Exadata, Goldengate, EBS ve linux Danışmanlık ve Eğitim için  [email protected] a mail atabilirsiniz.

Leave a Reply