Databases Reference
In-Depth Information
entries for that partition are now invalid because they now contain obsolete
ROWID
entries. The index
partition needs to be rebuilt to reflect the new
ROWID
values.
As with the truncate example, Oracle has no knowledge of the table partition boundaries for the
globally partitioned and non-partitioned indexes on the table, and therefore needs to mark the entire
index
UNUSABLE
, and the entire index needs to be rebuilt. For the locally partitioned index, only the index
partition of the partition being moved needs to be rebuilt.
In the following example, you're moving the older 1995 employee data to its own tablespace,
presumably so it can be made read-only:
SQL> alter table employees_parttest move partition p1995 tablespace emp1995_s;
Table altered.
INDEX_NAME NULL STATUS
------------------------------ ------------------------------ --------
EMPLOYEES_PARTTEST_GI1 MANAGER_100 UNUSABLE
EMPLOYEES_PARTTEST_GI1 MANAGER_500 UNUSABLE
EMPLOYEES_PARTTEST_GI1 MANAGER_900 UNUSABLE
EMPLOYEES_PARTTEST_GI1 MANAGER_MAX UNUSABLE
EMPLOYEES_PARTTEST_I1 UNUSABLE
EMPLOYEES_PART_LI1 PI1990 USABLE
EMPLOYEES_PART_LI1 PI1995 UNUSABLE
EMPLOYEES_PART_LI1 PI2000 USABLE
Splitting a Partition
Splitting a partition is usually done because a table's partition no longer meets the application
requirements or needs to be split for maintenance reasons. One of the most common reasons is simply
to add a partition to a table when it is not the high-end partition of a table, and because of that, it needs
to be done via a split rather than a partition add operation.
The following example splits the
MAXVALUE
partition in order to add a partition for data older than
January 2000. For your locally partitioned index, since the split operation touches the
PIMAX
index
partition and also creates a new one (P1999), the rows in the
PMAX
partition have been split between the
two partitions. Therefore, the index entries for the
PMAX
partition are obsolete since some data
presumably moved from the
PMAX
table partition to the new P1999 table partition. In the case of this split
operation, then, both local index partitions have been marked
UNUSABLE
. Since the new partition
previously did not exist, there really isn't an existing local index partition, so Oracle creates one and it is
automatically marked
UNUSABLE
.
As with previous operations such as
truncate
and
move
, all globally partitioned and non-partitioned
indexes have been marked entirely
UNUSABLE
.
SQL> ALTER TABLE employees_parttest SPLIT PARTITION Pmax at ('2000-01-01') INTO
2 (partition P1999 tablespace users,
3 partition pmax tablespace users);
Table altered.
