Dropping partitioned tables is similar to dropping nonpartitioned tables.
Oracle Database processes a
TABLE statement for a partitioned table in the same way that it processes the statement for a nonpartitioned table. One exception is when you use the
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
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.
Viewing Information About Partitioned Tables and Indexes for a list of these views that contain information related to partitioning
Oracle Database SQL Language Reference for the syntax of the
DROP TABLE statement
Oracle Database Reference for a description of the data dictionary views mentioned in this section