Manage table columns

Manage table column table operations 1. Change column Definitions

alter table .....modify

 

2. Add Columns

Alter table hr. admin_emp ADD (bonus NUMBER (); if you ADD columns to the table of basic compression, you cannot specify the default value. If you add a column to the table of oltp compression, you can add the default value, but the column must be non-null (not null)

 

3. rename a column

ALTER TABLE hr.admin_emp      RENAME COLUMN comm TO commission;Oracle Database also lets you rename column constraints.ALTER TABLE dept    RENAME CONSTRAINT dname_ukey TO dname_unikey;

 

4. Delete Columns

ALTER TABLE hr.admin_emp DROP COLUMN sal;ALTER TABLE hr.admin_emp DROP (bonus, commission);If you enable OLTP compression on a table, you can drop table columns.If you enable basic compression only, you cannot drop columns.

 

5. The ID column is unavailable.

ALTER TABLE hr.admin_emp SET UNUSED (hiredate, mgr);USER_UNUSED_COL_TABS,  ALL_UNUSED_COL_TABS ，dba_UNUSED_COL_TABS

 

-------- For external tables, it is equivalent to dropping to remove unavailable columns:

ALTER TABLE hr.admin_emp DROP UNUSED COLUMNS CHECKPOINT 250;