Problem

Cannot drop a tablespace with partitioned tables using “DROP TABLESPACE”

SQL> drop tablespace DATA_PARTS_2011Q1;
drop tablespace DATA_PARTS_2011Q1
*
ERROR at line 1:
ORA-01549: tablespace not empty, use INCLUDING CONTENTS option

If the tablespace has partitioned tables, those tables cannot be dropped either.  The subpartitions must be dropped first, then the partitions, the table and finally the tablespace can be dropped.

Solution

First, determine the structure of the tablespace, its segments, extents, tables, indexes, partitions, and subpartitions for those tables and indexes:

Take a look at the tablespace’s segements, extents,  and tables, to determine what needs to be dropped.

select * from dba_segments
where tablespace_name = ‘DATA_PARTS_2012Q’;

select * from dba_extents
where tablespace_name = ‘DATA_PARTS_2011Q’;

select * from dba_tables
where tablespace_name = ‘DATA_PARTS_2011Q’;

Next, look at the partitions:

select * from DBA_TAB_PARTITIONS
where tablespace_name = ‘DATA_PARTS_2011Q’;

select * from DBA_TAB_SUBPARTITIONS
where tablespace_name = DATA_PARTS_2011Q’;

Don’t forget to look for indexes and their partitions:

select * from dba_indexes
where tablespace_name = ‘DATA_PARTS_2011Q’;

select * from DBA_IND_PARTITIONS
where tablespace_name = ‘DATA_PARTS_2011Q’;

And don’t forget the subpartitions:

select * from DBA_IND_SUBPARTITIONS
where tablespace_name = ‘DATA_PARTS_2011Q’;

select * from DBA_TAB_SUBPARTITIONS
where tablespace_name = ‘DATA_PARTS_2011Q1’;

Start dropping subpartitions

Run the script below to generate the drop statements:
select ‘ALTER TABLE ‘ || TABLE_OWNER || ‘.’ || TABLE_NAME || ‘ DROP SUBPARTITION ‘ || SUBPARTITION_NAME  || ‘;’
from DBA_TAB_SUBPARTITIONS
where tablespace_name = ‘DATA_PARTS_2011Q1’;

Run the generated statements:
ALTER TABLE CISADM.D1_MSRMT_LOG_PARM DROP SUBPARTITION D1MSRMTLOGPARM_201103_SUB6;
ALTER TABLE CISADM.D1_MSRMT_LOG_PARM DROP SUBPARTITION D1MSRMTLOGPARM_201103_SUB7;
ALTER TABLE CISADM.D1_MSRMT_LOG_PARM DROP SUBPARTITION D1MSRMTLOGPARM_201103_SUB8;

Do the same if there are any index subpartitions.

Oracle will not drop the last subpartition in a partition, so this error is fine, it means that the partition can now be dropped
ALTER TABLE CISADM.D1_MSRMT_LOG_PARM DROP SUBPARTITION D1MSRMTLOGPARM_201103_SUB8
*
ERROR at line 1:
ORA-14629: cannot drop the only subpartition of a partition

Run the script below to generate the statements to drop the partitions
select  ‘ALTER TABLE ‘ || TABLE_OWNER || ‘.’ || TABLE_NAME || ‘ DROP PARTITION ‘ || PARTITION_NAME  || ‘;’
from DBA_TAB_PARTITIONS
where tablespace_name = ‘DATA_PARTS_2011Q’;

Run the generated statements
ALTER TABLE CISADM.D1_MSRMT_LOG_PARM DROP PARTITION D1MSRMTLOGPARM_201103;
ALTER TABLE CISADM.D1_MSRMT_LOG_PARM DROP PARTITION D1MSRMTLOGPARM_201102;
ALTER TABLE CISADM.D1_MSRMT_LOG_PARM DROP PARTITION D1MSRMTLOGPARM_201101;
ALTER TABLE CISADM.D1_MSRMT_LOG_PARM DROP PARTITION D1MSRMTLOGPARM_201012;

Do the same for any index partitions.

With all the partitions gone,  you can now drop the table

DROP TABLE D1_MSRMT_LOG_PARM;

And now drop the tablespace

DROP TABLESPACE DATA_PARTS_2011Q;