When to drop a partition from a table?
Afterward, you must rebuild any global indexes (whether partitioned or not) because the index (or index partitions) has been marked UNUSABLE. The following statements provide an example of dropping partition dec98 from the sales table, then rebuilding its global nonpartitioned index.
When to use partition or delete in Oracle?
In the same way, when PARTITION is used with DELETE, only rows in the partition or partitions named in the partition list are checked for deletion. For statements that insert rows, the behavior differs in that failure to find a suitable partition causes the statement to fail.
How to drop partitions in a table TMP?
A table TMP has 5 partitions, namely P_1, P_2,….P_5. I need to drop some partitions of TMP; the partitions to drop are derived by another query. ALTER TABLE TMP DROP PARTITIONS (SELECT From //expression to get partition names ) Let’s say the SELECT statement returns P_1 & P_5. The part query of the ALTER statement above doesn’t work.
Can a table have more than one partition?
You specify UPDATE INDEXES (Cannot be specified for index-organized tables. Use UPDATE GLOBAL INDEXES instead.) If a table contains only one partition, you cannot drop the partition. Instead, you must drop the table.
How to drop a table partition without global indexes?
If the partition contains data and one or more global indexes are defined on the table, then use one of the following methods (method 1, 2 or 3) to drop the table partition. Issue the ALTER TABLE DROP PARTITION statement without maintaining global indexes.
Is it possible to rebuild all partitions of an index?
If index sales_area_ix were a range-partitioned global index, then all partitions of the index would require rebuilding. Further, it is not possible to rebuild all partitions of an index in one statement. You must issue a separate REBUILD statement for each partition in the index.