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


© Copyright SS64.com 1999-2013
Some rights reserved