Databases Reference
In-Depth Information
Once you have already dropped the global index partition, you must issue an
ALTER INDEX
command
to rebuild the partition that was marked
UNUSABLE
due to the
DROP PARTITION
operation.
SQL> ALTER INDEX employees_i2 rebuild partition manager_300;
Index altered.
Since it is a requirement to specify a partition with
MAXVALUE
on a globally partitioned index, you can
never drop the highest partition. For example,
SQL> ALTER INDEX employees_i2
2 DROP PARTITION manager_max;
DROP PARTITION manager_max
*
ERROR at line 2:
ORA-14078: you may not drop the highest partition of a GLOBAL index
Globally partitioned indexes can be unique or non-unique. So far, you have only created a non-
unique index. See the following to create a unique globally partitioned index on a table:
SQL> create unique index employees_uk1
2 on employees (manager_id, employee_id)
3 global
4 partition by range(manager_id)
5 (partition manager_100 values less than (100),
6 partition manager_200 values less than (200),
7 partition manager_300 values less than (300),
8 partition manager_400 values less than (400),
9 partition manager_500 values less than (500),
10 partition manager_600 values less than (600),
11 partition manager_700 values less than (700),
12 partition manager_800 values less than (800),
13 partition manager_900 values less than (900),
14* partition manager_max values less than (maxvalue));
Index created.
Unlike locally partitioned indexes that can be defined as prefixed or non-prefixed, all globally
partitioned indexes must be created as prefixed; that is, the partitioning column must be on the leading
edge of the index. If you try to create a non-prefixed globally partitioned index, you will receive the
following error:
SQL> create unique index employees_uk1
2 on employees (employee_id)
3 global
4 partition by range(manager_id)
5 (partition manager_100 values less than (100),
6 partition manager_200 values less than (200),
7 partition manager_300 values less than (300),
8 partition manager_400 values less than (400),
9 partition manager_500 values less than (500),
