ALTER TABLE

Change the properties of an existing table.

Syntax:

   ALTER TABLE [schema.]table
      RENAME TO new_table_name

   ALTER TABLE [schema.]table
      [ [NO]MINIMISE RECORDS PER BLOCK ]
          [PARALLEL parallel_clause]
             [ENABLE enable_clause | DISABLE disable_clause]
                 [{ENABLE|DISABLE} TABLE LOCK]
                    [{ENABLE|DISABLE} ALL TRIGGERS]

   ALTER TABLE [schema.]table
      iot_overflow_clause
          [PARALLEL parallel_clause]
             [ENABLE enable_clause | DISABLE disable_clause]
                 [{ENABLE|DISABLE} TABLE LOCK]
                    [{ENABLE|DISABLE} ALL TRIGGERS]

   ALTER TABLE [schema.]table
      partitioning_clause
          [PARALLEL parallel_clause]
             [ENABLE enable_clause | DISABLE disable_clause]
                 [{ENABLE|DISABLE} TABLE LOCK]
                    [{ENABLE|DISABLE} ALL TRIGGERS]

   ALTER TABLE [schema.]table
      tbl_defs,...
          [PARALLEL parallel_clause]
             [ENABLE enable_clause | DISABLE disable_clause]
                 [{ENABLE|DISABLE} TABLE LOCK]
                    [{ENABLE|DISABLE} ALL TRIGGERS]

tbl_defs:
   ADD [column datatype] [DEFAULT expr] [column_constraint(s)]
     [table_constraint] [table_ref_constraint]

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

   MODIFY [table_constraint]

   drop_column_clause

   DROP drop_constraint_clause

   [PCTFREE int][PCTUSED int][INITTRANS int]
      [MAXTRANS int][STORAGE storage_clause]

   extent_options

   MOVE [ONLINE] storage_options INDEX index_organized_tbl_clause
      [LOB_storage_clause][varray_clause]

   LOGGING|NOLOGGING

   MODIFY NESTED TABLE collection_item RETURN AS {LOCATOR|VALUE}

   MODIFY LOB [LOB_storage_clause]

   MODIFY VARRAY [varray_clause]

   CACHE | NOCACHE

   MONITORING | NOMONITORING

storage_options:
   PCTFREE int
   PCTUSED int
   INITTRANS int
   MAXTRANS int
   STORAGE storage_clause
   TABLESPACE tablespace
   [LOGGING|NOLOGGING]

extent_options:
   ALLOCATE EXTENT [( [size int K | M ]
      [DATAFILE 'filename' ] [INSTANCE int] )]
   DEALLOCATE UNUSED [KEEP int K | M ]

index_organized_tbl_clause:
   storage_option(s) [PCTTHRESHOLD int]
     [COMPRESS int|NOCOMPRESS]
         [ [INCLUDING column_name] OVERFLOW [storage_option(s)] ]

iot_overflow_clause:
   {PCTTHRESHOLD int | INCLUDING column} |
       OVERFLOW overflow_storage_clause
          ADD OVERFLOW [storage_options] [(PARTITION storage_options)]

overflow_storage_clause:
   PCTFREE int
   PCTUSED int
   INITTRANS int
   MAXTRANS int
   extent_options
   STORAGE storage_clause
   [LOGGING|NOLOGGING]

nested_storage_clause:
   NESTED TABLE nested_item STORE AS storage_table
      [RETURN AS {LOCATOR|VALUE} ]

drop_column_clause:
   SET UNUSED (column,...)
      [CASCADE CONSTRAINTS][INVALIDATE]

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

   DROP {UNUSED COLUMNS|COLUMNS CONTINUE} [CHECKPOINT int]

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;

"The first 90% of a project takes 90% of the time, the last 10% takes the other 90% of the time" - Murphy's Law

Related Commands:

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_PART_TABLES      ALL_PART_TABLES      USER_PART_TABLES  
                                                                DICTIONARY
                                                                DICT_COLUMNS