Alter Table Reference

 

Examples are offered instead of complete explanations for easy copy and paste.

Rename a Table


ALTER TABLE old_name RENAME TO new_name;

Add a Column to a Table

Add one column at a time.


ALTER TABLE TABLE_NAME ADD column_name varchar2(50);

Add multiple columns.


ALTER TABLE TABLE_NAME ADD
(    new_col_1     NUMBER,
     new_col_2     varchar2(20)
);

Remove (Drop) a Column


ALTER TABLE TABLE_NAME DROP COLUMN useless_column;

Rename a Column


ALTER TABLE TABLE_NAME RENAME COLUMN old_col_name TO new_col_name;

Adjust a Column’s Data Type

Adjust one column at a time.


ALTER TABLE TABLE_NAME MODIFY column_name varchar2(10);
ALTER TABLE TABLE_NAME MODIFY column_name varchar2(20);
ALTER TABLE TABLE_NAME MODIFY column_name varchar2(30) NOT NULL;

Adjust multiple columns. This is just like adding columns, only it uses the keyword MODIFY instead of ADD.


ALTER TABLE TABLE_NAME MODIFY
(    adjusted_col_1     varchar2(33),
     adjusted_col_2     varchar2(20) NOT NULL
);
 Posted by at 9:42 pm

 Leave a Reply

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

(required)

(required)