FROM: http://ss64.com/ora/table_a_part.html
ALTER TABLE partitioning
Change the partition properties of an existing table.
Syntax:
ALTER TABLE [schema.]table partitioning_clause [PARALLEL parallel_clause] [ENABLE enable_clause | DISABLE disable_clause] [{ENABLE|DISABLE} TABLE LOCK] [{ENABLE|DISABLE} ALL TRIGGERS]; partitioning_clause: ADD PARTITION partition --add Range ptn VALUES LESS THAN (value, value, [MAXVALUE],...) [partition_description] ADD PARTITION partition --add Hash ptn storage_options extent_options OVERFLOW storage_options extent_options COMPRESS | NOCOMPRESS LOB (LOB_item) STORE AS LOB_segname VARRAY varray STORE AS LOB_segname {UPDATE | INVALIDATE} GLOBAL INDEXES PARALLEL int | NOPARALLEL ADD PARTITION partition --add List ptn VALUES (DEFAULT | NULL | value [,…]) [partition_description] COALESCE PARTITION partition {UPDATE | INVALIDATE} GLOBAL INDEXES PARALLEL int | NOPARALLEL DROP [SUB]PARTITION partition {UPDATE | INVALIDATE} GLOBAL INDEXES PARALLEL int | NOPARALLEL MODIFY PARTITION partition storage_options extent_options OVERFLOW storage_options extent_options COMPRESS | NOCOMPRESS LOB (LOB_item) STORE AS LOB_segname VARRAY varray STORE AS LOB_segname ADD SUBPARTITION subpartition [VALUES (DEFAULT | NULL | value [,…])] TABLESPACE tablespace OVERFLOW [TABLESPACE tablespace] LOB (LOB_item) STORE AS LOB_segname VARRAY varray STORE AS LOB_segname [{UPDATE | INVALIDATE} GLOBAL INDEXES] [PARALLEL int | NOPARALLEL] COALESCE SUBPARTITION -- for HASH Partition [{UPDATE | INVALIDATE} GLOBAL INDEXES] [PARALLEL int | NOPARALLEL] MAPPING TABLE UPDATE BLOCK REFERENCES MAPPING TABLE ALLOCATE EXTENT [( [size int K | M ] [DATAFILE 'filename' ] [INSTANCE int] ) MAPPING TABLE DEALLOCATE UNUSED [KEEP int K | M ] {ADD | DROP} VALUES (partition_value,...) -- for List Partition [REBUILD] UNUSABLE LOCAL INDEXES MODIFY DEFAULT ATTRIBUTES [FOR PARTITION partition] storage_options [PCTTHRESHOLD int] [[NO]COMPRESS [int]] [overflow_clause] [LOB lob_item LOB_parameters][VARRAY varray LOB_parameters] MODIFY SUBPARTITION subpartion ALLOCATE EXTENT [( [size int K | M ] [DATAFILE 'filename' ] [INSTANCE int] ) DEALLOCATE UNUSED [KEEP int K | M ] {ADD | DROP} VALUES (partition_value,...) LOB (LOB_item) STORE AS LOB_segname VARRAY varray STORE AS LOB_segname {ADD | DROP} VALUES (partition_value,...) -- for List Partition [REBUILD] UNUSABLE LOCAL INDEXES MOVE PARTITION partition [MAPPING TABLE] storage_options [COMPRESS [int] | NOCOMPRESS] OVERFLOW storage_options LOB (LOB_item) STORE AS LOB_segname VARRAY varray STORE AS LOB_segname SUBPARTITIONS hash_subpartition_quantity [STORE IN (tablespace,...)] (SUBPARTITION subpartition [VALUES (DEFAULT | NULL | value,...)] TABLESPACE tablespace OVERFLOW [TABLESPACE tablespace] LOB (LOB_item) STORE AS LOB_segname VARRAY varray STORE AS LOB_segname ,SUBPARTITION...) {UPDATE | INVALIDATE} GLOBAL INDEXES {NOPARALLEL|PARALLEL int} MOVE SUBPARTITION subpartion [partition_description] VALUES (DEFAULT | NULL | value [,…]) TABLESPACE tablespace OVERFLOW [TABLESPACE tablespace] LOB (LOB_item) STORE AS LOB_segname VARRAY varray STORE AS LOB_segname {UPDATE | INVALIDATE} GLOBAL INDEXES {NOPARALLEL|PARALLEL int} RENAME [SUB]PARTITION ptn_name TO new_name TRUNCATE [SUB]PARTITION ptn_name {DROP|REUSE} STORAGE {UPDATE | INVALIDATE} GLOBAL INDEXES {NOPARALLEL|PARALLEL int} SET SUBPARTITION TEMPLATE (SUBPARTITION subpartition [VALUES (DEFAULT | NULL | value [,…])] TABLESPACE tablespace OVERFLOW [TABLESPACE tablespace] LOB (LOB_item) STORE AS LOB_segname VARRAY varray STORE AS LOB_segname ) SET SUBPARTITION TEMPLATE int --hash SubPartition quantity SPLIT PARTITION ptn_name AT (value, value...) INTO (ptn_spec, ptn_spec) {UPDATE | INVALIDATE} GLOBAL INDEXES {NOPARALLEL|PARALLEL int} SPLIT SUBPARTITION ptn_name VALUES (value, [NULL],value [,…]) INTO (ptn_spec, ptn_spec) {UPDATE | INVALIDATE} GLOBAL INDEXES {NOPARALLEL|PARALLEL int} MERGE PARTITIONS ptn_1, ptn_2 INTO PARTITION partition storage_options extent_options OVERFLOW storage_options extent_options COMPRESS | NOCOMPRESS LOB (LOB_item) STORE AS LOB_segname VARRAY varray STORE AS LOB_segname SUBPARTITIONS hash_subpartition_quantity [STORE IN (tablespace [,…])] (SUBPARTITION subpartition [VALUES (DEFAULT | NULL | value [,…])] TABLESPACE tablespace OVERFLOW [TABLESPACE tablespace] LOB (LOB_item) STORE AS LOB_segname VARRAY varray STORE AS LOB_segname ,SUBPARTITION...) {UPDATE | INVALIDATE} GLOBAL INDEXES {NOPARALLEL | PARALLEL int} MERGE SUBPARTITIONS subptn_1, subptn_2 INTO SUBPARTITION subpartition VALUES LESS THAN (value, value, [MAXVALUE] [,…]) TABLESPACE tablespace OVERFLOW [TABLESPACE tablespace] LOB (LOB_item) STORE AS LOB_segname VARRAY varray STORE AS LOB_segname ,SUBPARTITION... {UPDATE | INVALIDATE} GLOBAL INDEXES {NOPARALLEL | PARALLEL int} EXCHANGE [SUB]PARTITION [sub]partition WITH TABLE table {INCLUDING|EXCLUDING} INDEXES {WITH|WITHOUT} VALIDATION EXCEPTIONS INTO [schema.]table {UPDATE | INVALIDATE} GLOBAL INDEXES {NOPARALLEL|PARALLEL int}storage_options: PCTFREE int PCTUSED int INITTRANS int MAXTRANS int STORAGE storage_clause TABLESPACE tablespace {LOGGING|NOLOGGING}overflow_clause: OVERFLOW ALLOCATE EXTENT [( [size int K | M ] [DATAFILE 'filename' ] [INSTANCE int] )] OVERFLOW DEALLOCATE UNUSED [KEEP int K | M ] ADD OVERFLOW storage_options [(PARTITION storage_options [,PARTITION storage_options [,…]])]extent_options: 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;
"You're either part of the solution or part of the problem" - Eldridge Cleaver
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_TAB_PARTITIONS ALL_TAB_PARTITIONS USER_TAB_PARTITIONS DBA_PART_TABLES ALL_PART_TABLES USER_PART_TABLES
