ALTER TABLE columns

Add, modify or drop columns and column properties

Syntax:

   ALTER TABLE [schema.]table
      column_clauses,…
         [ENABLE enable_clause | DISABLE disable_clause]
              [{ENABLE|DISABLE} TABLE LOCK]
                 [{ENABLE|DISABLE} ALL TRIGGERS];

 column_clauses:
   ADD ( column datatype [DEFAULT expr] [column_constraint(s)] [,…] )
     [table_constraint] [table_ref_constraint] [lob_storage]

   SET UNUSED (column,…)
      [CASCADE CONSTRAINTS ][INVALIDATE]

   DROP COLUMN column
      [CASCADE CONSTRAINTS] [INVALIDATE] CHECKPOINT int

   DROP (column,…)
      [CASCADE CONSTRAINTS] [INVALIDATE] CHECKPOINT int

   DROP COLUMNS CONTINUE [CHECKPOINT int]

   DROP UNUSED COLUMNS [CHECKPOINT int]

   MODIFY column datatype [DEFAULT expr] [column_constraint(s)]

   MODIFY column [NOT] SUBSTITUTABLE AT ALL LEVELS [FORCE]

   MODIFY NESTED TABLE collection_item RETURN AS {LOCATOR | ITEM}

   MODIFY LOB (lob_item) (modify_LOB_parameters)

   MODIFY VARRAY varray_item (modify_LOB_parameters)

   RENAME COLUMN column TO new_name

modify_LOB_parameters:

   PCTVERSION int
   RETENTION
   FREEPOOLS int
   REBUILD FREEPOOLS
   CACHE 
   CACHE READS [LOGGING|NOLOGGING]
   NOCACHE [LOGGING|NOLOGGING]
   ALLOCATE EXTENT [( [size int K | M ]
      [DATAFILE 'filename' ]
        [INSTANCE int] )]
   DEALLOCATE UNUSED [KEEP int K | M ]

Examples

Add a column to a table
   ALTER TABLE STAFF_OPTIONS
      ADD SO_INSURANCE_PROVIDER Varchar2(35);

Add  a default value to a column
   ALTER TABLE STAFF_OPTIONS
      MODIFY SO_INSURANCE_PROVIDER Varchar2(35) DEFAULT 'ABC Ins';

Add two columns to a table and remove a constraint
   ALTER TABLE STAFF_OPTIONS
      ADD (SO_STAFF_ID INT, SO_PENSION_ID INT)
          STORAGE INITIAL 10 K
          NEXT 10 K
          MAXEXTENTS 121
          PCTINCREASE 0
          FREELISTS 2
      DROP CONSTRAINT cons_SO;

"Fools ignore complexity; pragmatists suffer it; experts avoid it; geniuses remove it” ~ Alan Perlis

ANALYZE TABLE COMPUTE STATISTICS
ALTER INDEX
ALTER VIEW
COMMENT - Add a comment to a table or a column.
RENAME
DBMS_REDEFINITION

Related Views:

  DBA_ALL_TABLES       ALL_ALL_TABLES       USER_ALL_TABLES
  DBA_TABLES           ALL_TABLES           USER_TABLES         TAB
  DBA_TAB_COLUMNS      ALL_TAB_COLUMNS      USER_TAB_COLUMNS 
  DBA_TAB_COLS         ALL_TAB_COLS         USER_TAB_COLS 
                                                                DICT_COLUMNS