I have some code that drops a tablespace if it is empty, and in this situation the code is called after dropping a table with a single dedicated tablespace. This table is partitioned with 30 partitions, all residing in the same tablespace. The database is 10G Release 2 on a RAC system, and I’m really not sure if RAC, or the partitioning, has any bearing on this issue.

The recyclebin is enabled for the database and so in order to be able to drop the tablespace after the table is dropped (i.e. I wanted an empty tablespace and a normal drop leaves the original objects with their names renamed), I decided to use the “PURGE” option. It seemed easier than dealing with the recyclebin directly, or disabling it.

So I’m using a “DROP TABLE tablename PURGE;” command using dynamic SQL from a PL/SQL package. The table drops, but the partition segments remain with a segment type of ‘TEMPORARY’. This became a problem because my PL/SQL process then attempts to drop the table’s tablespace if it is empty (i.e. no segments), and this test fails because of the temporary segments.

The tablespace will still drop with the temporary segments with the command “DROP TABLESPACE tablespace_name INCLUDING CONTENTS AND DATAFILES;’, but the original check I coded fails and throws a user defined exception. I couldn’t find anything in Metalink that talks about this phenomenon, and ultimately I decided to just change the “tablespace is empty” check to:

SELECT COUNT(*)

INTO l_segcnt

FROM dba_segments

WHERE tablespace_name = p_tsnam

AND segment_type != 'TEMPORARY';

With the added segment type check the code attempts the drop and the database allows the tablespace to be dropped, so everything is fine. However, it just doesn’t seem to be as bullet proof as I’d like.