ALTER INDEX

Change the storage properties of a cluster.

Syntax:

   ALTER INDEX [schema.]index options

options:
The options used with this command can be any combination of the following

    ENABLE
    DISABLE
    COALESCE
    UNUSABLE
    RENAME TO new_index_name

    [NO]LOGGING
    PCTFREE int
    PCTUSED int
    INITRANS int
    MAXTRANS int
    STORAGE storage_clause

    ALLOCATE EXTENT [SIZE int K | M]
    ALLOCATE EXTENT [DATAFILE 'filename']
    ALLOCATE EXTENT [INSTANCE int]

    DEALLOCATE UNUSED
    DEALLOCATE UNUSED KEEP int K | M

    NOPARALLEL
    PARALLEL int

    REBUILD NOREVERSE [rebuild_options]
    REBUILD REVERSE [rebuild_options]
    REBUILD [SUB]PARTITION partition [rebuild_options]
    REBUILD PARTITION partition [rebuild_options]
    REBUILD PARAMETERS [parameter_options]

    rebuild_options:
        ONLINE
        COMPUTE STATISTICS
        TABLESPACE tablespace_name
        NOPARALLEL
        PARALLEL int
        [NO]LOGGING
        COMPRESS int
        NOCOMPRESS
        PCTFREE int
        PCTUSED int
        INITRANS int
        MAXTRANS int
        STORAGE storage_clause

    MODIFY PARTITION partition COALESCE
    MODIFY PARTITION partition UNUSABLE
    MODIFY PARTITION partition partition_options

    partition_options:
        ALLOCATE EXTENT [SIZE int K | M]
        ALLOCATE EXTENT [DATAFILE 'filename']
        ALLOCATE EXTENT [INSTANCE int]
        DEALLOCATE UNUSED
        DEALLOCATE UNUSED KEEP int K | M
        [NO]LOGGING
        PCTFREE int
        PCTUSED int
        INITRANS int
        MAXTRANS int
        STORAGE storage_clause

    MODIFY DEFAULT ATTRIBUTES [FOR PARTITION partition] attrib_options

    attrib_options:
        TABLESPACE {tablespace|DEFAULT}
        [NO]LOGGING
        PCTFREE int
        PCTUSED int
        INITRANS int
        MAXTRANS int
        STORAGE storage_clause

    RENAME [SUB]PARTITION partition old_name TO new_name
    DROP PARTITION partition_name
    SPLIT PARTITION partition_name AT (value list) INTO (ptn_descr1, ptn_descr2) [NOPARALLEL|PARALLEL int]

    ptn_descr:
        PARTITION [partition [NOCOMPRESS|COMPRESS int|segment_attributes_clause]]

    MODIFY SUBPARTITION subpartition UNUSABLE
    MODIFY SUBPARTITION subpartition sub_partition_options

    sub_partition_options:
        ALLOCATE EXTENT [SIZE int K | M]
        ALLOCATE EXTENT [DATAFILE 'filename']
        ALLOCATE EXTENT [INSTANCE int]
        DEALLOCATE UNUSED
        DEALLOCATE UNUSED KEEP int K | M

More than one ALLOCATE EXTENT option should be specified in the same clause e.g.
ALLOCATE EXTENT SIZE 200K Datafile 'MyFile.idx'

"We trained hard, but it seemed that every time we were beginning to form up into teams we would be reorganised. I was to learn later in life that we tend to meet any new situation by re-organising, and a wonderful method it can be for creating the illusion of progress while producing confusion, inefficency and demoralisation" - Caius Petronius (A.D. 66)

Related Commands:

ANALYZE INDEX COMPUTE STATISTICS
CREATE INDEX
DROP INDEX

Related Views:

 DBA_INDEXES            ALL_INDEXES            USER_INDEXES 
                                                                INDEX_HISTOGRAM 
                                                                INDEX_STATS 
 DBA_INDEXTYPES           ALL_INDEXTYPES           USER_INDEXTYPES 
 DBA_INDEXTYPE_OPERATORS  ALL_INDEXTYPE_OPERATORS  USER_INDEXTYPE_OPERATORS 
 DBA_IND_COLUMNS        ALL_IND_COLUMNS        USER_IND_COLUMNS 
 DBA_IND_EXPRESSIONS    ALL_IND_EXPRESSIONS    USER_IND_EXPRESSIONS 
 DBA_IND_PARTITIONS     ALL_IND_PARTITIONS     USER_IND_PARTITIONS 
 DBA_IND_SUBPARTITIONS  ALL_IND_SUBPARTITIONS  USER_IND_SUBPARTITIONS