Table of Contents
The structure of the table can be modified by using ALTER TABLE command. ALTER TABLE works by making a temporary copy of the original table. The alteration is performed on the copy, then the original table is deleted and the new one is renamed.
- ALTER TABLE changing the structure of an existing table
- ALTER TABLE add or delete columns
- ALTER TABLE create or destroy indexes
- ALTER TABLE change the data type of existing columns
- ALTER TABLE rename columns or table itself
- To use ALTER TABLE, the ALTER, INSERT and CREATE privileges for the table are required
- While ALTER TABLE IS executing, the original table is still readable by users of the oracle
Adding new columns
ALTER TABLE <TableName> ADD(<NewColumnName 1> <DataType> (<Size>), <NewColumnName 2> <DataType> (<Size>), .............. )
ALTER TABLE EMPLOYEE ADD (COUNTRY VARCHAR2(25));
Drop column from table
ALTER TABLE <TableName> DROP COLUMN <ColumnName>
ALTER TABLE EMPLOYEE DROP COLUMN COUNTRY
Modify existing column in table
ALTER TABLE <TableName> MODIFY ( <ColumnName> <NewDataType> (<NewSize>));
ALTER TABLE EMPLOYEE MODIFY ( COUNTRY VARCHAR2(30));
The following tasks can not be performed when using the ALTER TABLE clause.
- Change the name of the column.
- Change the name of the table
- Decrease the size of a column if the table data exists
Hello! I am Narayanaswamy founder and admin of narayanatutorial.com. I have been working in IT industry more than 7 years. NarayanaTutorial is my web technologies blog. My specialties are Java / J2EE, Spring, Hibernate, Struts, Webservices, PHP, Oracle, MySQL, SQLServer, Web Hosting and Website Development.
I am a self learner and passionate about training and writing. I am always trying my best to share my knowledge through my blog.