Problem
Cannot drop a tablespace with a partitioned tables until all the subpartitions and partitions are dropped first.
drop tablespace DATA_PARTS_2011Q1
*
ERROR at line 1:
ORA-01549: tablespace not empty, use INCLUDING CONTENTS option
Solution
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’;
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
Nice. This implies that you want to keep the partitions for the table that do not live in that particular tablespace, I assume.
LikeLike