The Oracle (tm) Users' Co-Operative FAQ
What happens if I drop a partition from a partitioned table whilst a query is running ?
| Author's name: Jonathan Lewis Author's Email: Jonathan@jlcomp.demon.co.uk |
Date written: 1st Nov 2002 Oracle version(s): 8.1 |
| What happens if I drop a partition from a partitioned table whilst a query is running, and the query is going to have to visit the dropped partition ? |
The documentation on partitioned tables explains that a select may execute and complete successfully across a drop partition even if the partition dropped is a target of the select statement.
I have tested this out and found that it can work. However, if you drop a partition, then allocate the space it was using to another data segment before the query reaches it, then the query will (quite reasonably) crash.Therefore the guaranteed behaviour seems to be that the query will either be correct or the query will crash. (In fact, in a very early verion (I think 8.0.3/4) I found that some queries simply stopped when they hit the destroyed partition and returned whatever result they had accumulated up to that point.)
Note - If you drop partitions at a high rate, and have a large number of partitions, you may find you get a significant parse-time overhead, because the cursors referencing a partitioned object become invalid when a partition is dropped (or subject to virtiually any other DDL-style maintenance).
Further reading: N/A
