Change the properties of an index.
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 [NO]MONITORING USAGE UPDATE BLOCK REFERENCES NOPARALLEL PARALLEL int MODIFY PARTITION partition COALESCE MODIFY PARTITION partition UNUSABLE MODIFY PARTITION partition UPDATE BLOCK REFERENCES MODIFY PARTITION partition PARAMETERS ('alter_partition_params') 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 RENAME [SUB]PARTITION old_name TO new_name DROP PARTITION partition SPLIT PARTITION partition AT (value_list) [INTO (ptn_descr1, ptn_descr2)] [NOPARALLEL|PARALLEL int] ptn_descr: PARTITION [partition attrib_options] 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 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 REBUILD [rebuild_options] REBUILD NOREVERSE [rebuild_options] REBUILD REVERSE [rebuild_options] REBUILD [SUB]PARTITION partition [rebuild_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 PARAMETERS ('odci_parameters') STORAGE storage_clause
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 Oracle Commands:
ANALYZE INDEX COMPUTE STATISTICS
CREATE INDEX
DROP INDEX
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