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 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_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