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