Databases Reference
In-Depth Information
partition p1992 values less than ('1993-01-01'),
partition p1993 values less than ('1994-01-01'),
partition p1994 values less than ('1995-01-01'),
partition p1995 values less than ('1996-01-01'),
partition p1996 values less than ('1997-01-01'),
partition p1997 values less than ('1998-01-01'),
partition p1998 values less than ('1999-01-01'),
partition p1999 values less than ('2000-01-01'),
partition p2000 values less than ('2001-01-01'),
partition p9999 values less than ('9999-12-31'),
partition pmax values less than (MAXVALUE);
Exchanging a Partition
Especially in the data warehouse environment, partition exchanges are common for large batch loads.
The loads are performed into a standalone table, so read operations are not affected during the load
operation. Then a partition exchange is done, which is essentially a data dictionary change to repoint a
standalone table segment to be part of a partitioned table and make the associated affected table
partition a standalone table segment.
A partition exchange is similar to performing a partition move in that only the affected, exchanged
partition of a locally partitioned index is marked unusable.
For the globally partitioned and non-partitioned indexes, you can see again that both entire indexes
have been marked
UNUSABLE
.
SQL> ALTER TABLE employees_parttest EXCHANGE PARTITION p1995
2 WITH TABLE employees_parttest_exch;
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 PIMAX USABLE
Dropping a Partition
Dropping a partition usually occurs for date- or timestamp-based partitioned tables and occurs when
the data is no longer needed because the data retention for the data has expired.
For locally partitioned indexes, there is no impact on any of the remaining local index partitions. All
local partitioned indexes remain in
USABLE
status. Once again, however, for the globally partitioned and
non-partitioned indexes, the entire indexes have been marked unusable, as Oracle can't determine for
either of these indexes which rows have been dropped via the partition drop operation.
