To avoid running into resource constraints, the
PURGE statement for a partitioned table drops the table in multiple transactions, where each transaction drops a subset of the partitions or subpartitions and then commits. The table is dropped at the conclusion of the final transaction.
This behavior comes with some changes to the
TABLE statement. First, if the
PURGE statement fails, then you can take corrective action, if any, and then reissue the statement. The statement resumes at the point where it failed. Second, while the
PURGE statement is in progress, the table is marked as unusable by setting the
STATUS column to the value
UNUSABLE in the following data dictionary views:
You can list all
UNUSABLE partitioned tables by querying the
STATUS column of these views.
Queries against other data dictionary views pertaining to partitioning, such as
DBA_TAB_SUBPARTITIONS, exclude rows belonging to an
UNUSABLE table. A complete list of these views is available in "Viewing Information About Partitioned Tables and Indexes".
After a table is marked
UNUSABLE, the only statement that can be issued against it is another
PURGE statement, and only if the previous
PURGE statement failed. Any other statement issued against an
UNUSABLE table results in an error. The table remains in the
UNUSABLE state until the drop operation is complete.