Add, modify or drop columns for an external table.
Syntax:
ALTER TABLE [schema.]table
xt_clauses [,…]
[ENABLE enable_clause | DISABLE disable_clause]
[PARALLEL parallel_clause]
[{ENABLE|DISABLE} TABLE LOCK]
[{ENABLE|DISABLE} ALL TRIGGERS]
REJECT LIMIT {int | UNLIMITED};
xt_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]
DEFAULT DIRECTORY directory [ACCESS PARAMETERS {USING CLOB subquery | (opaque_format_spec) }]
LOCATION (directory:'location_specifier' [,directory2:'location_specifier2'…)
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 abyss between rational and the spiritual, the external and the internal, the objective and the subjective, the technical and the moral, the universal and the unique, constantly grows deeper" ~ Vaclav Havel
Related Oracle 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_EXTERNAL_TABLES ALL_EXTERNAL_TABLES USER_EXTERNAL_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