ALTER TABLE properties

Change the name, physical and logical storage parameters of an existing table. Enable or disable Constraints, Triggers and Primary Keys.

Syntax:

   ALTER TABLE [schema.]table
      RENAME TO new_table_name;

   ALTER TABLE [schema.]table
      table_properties,...
         [alter_iot_clause]
            [PARALLEL parallel_clause]
               [{ENABLE|DISABLE} ROW MOVEMENT]
                  [ENABLE enable_clause | DISABLE disable_clause]
                      [{ENABLE|DISABLE} TABLE LOCK]
                         [{ENABLE|DISABLE} ALL TRIGGERS];

table_properties:

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

   ALLOCATE EXTENT [( [size int K | M ]
      [DATAFILE 'filename' ]
        [INSTANCE int] )]

   CACHE | NOCACHE

   COMPRESS | NOCOMPRESS

   DEALLOCATE UNUSED [KEEP int K | M ]

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

   LOGGING|NOLOGGING

   ADD SUPPLEMENTAL LOG GROUP log_group (column [,...]) [ALWAYS]

   DROP SUPPLEMENTAL LOG GROUP log_group

   MODIFY NESTED TABLE collection_item RETURN AS {LOCATOR|VALUE}

   MODIFY LOB [LOB_storage_clause]

   MONITORING | NOMONITORING

  {MINIMISE | NOMINIMISE} RECORDS PER BLOCK

   UPGRADE [[NOT] INCLUDING DATA ] column_properties

   MODIFY VARRAY [varray_clause]

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

alter_iot_clause:
   PCTTHRESHOLD int           [ [INCLUDING column_name] OVERFLOW [storage_options] ]
   COMPRESS int | NOCOMPRESS  [ [INCLUDING column_name] OVERFLOW [storage_options] ]
   COALESCE                   [ [INCLUDING column_name] OVERFLOW [storage_options] ]
   ADD OVERFLOW [storage_options]
      [(PARTITION storage_options [,PARTITION storage_options...])]
          [ [INCLUDING column_name] OVERFLOW [storage_options] ]
   MAPPING TABLE  [ [INCLUDING column_name] OVERFLOW [storage_options] ]
   NO MAPPING     [ [INCLUDING column_name] OVERFLOW [storage_options] ]
   MAPPING TABLE UPDATE BLOCK REFERENCES  [ [INCLUDING column_name] OVERFLOW [storage_options] ]
   MAPPING TABLE ALLOCATE EXTENT 
    [( [size int K | M ]
         [DATAFILE 'filename' ]
           [INSTANCE int] )]  [ [INCLUDING column_name] OVERFLOW [storage_options] ]
   MAPPING TABLE DEALLOCATE_extent_clause  [ [INCLUDING column_name] OVERFLOW [storage_options] ]

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;

Move a table to a different tablespace: (will then need to rebuild any indexes)
   ALTER TABLE STAFF_OPTIONS MOVE TABLESPACE PERSONNEL;

"Failure is Not an Option" - Gene Kranz (during the Apollo 13 mission)

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