Dropping an Uncooperative Oracle Tablespace

We recently had a client database that was under some space pressure.  After several steps to reorganize data and segregate applications by tablespace for ease of administration and management, a couple of "apperently" empty tablespaces remained.  The next step in the process was to drop these tablespaces and datafiles to recover space.  This is usually a relatively trivial matter, and can often be done expediently.  

However, every now and then, unusual problems can arise.  In this one case, a problematic tablespace, with no segments listed in the DBA_SEGMENTS view or the "Tablespaces" tab of Oracle Enterprise Manager, failed to drop, as follows:

SQL> drop tablespace team_index01 including contents and datafiles;
drop tablespace team_index01 including contents and datafiles
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-02429: cannot drop index used for enforcement of unique/primary key


SQL> select owner, segment_name
  2  from dba_extents
  3  where tablespace_name = 'TEAM_INDEX01';

no rows selected

SQL> !oerr ora 2429
02429, 00000, "cannot drop index used for enforcement of unique/primary key"
// *Cause: user attempted to drop an index that is being used as the
//         enforcement mechanism for unique or primary key.
// *Action: drop the constraint instead of the index.

The above led me to conclude that there is still some logical, though likely not physical, connection to this tablespace and a current database object, so:

SQL> select constraint_name, index_owner, index_name
  2  from dba_constraints
  3  where index_name in
  4  (select index_name from dba_indexes
  5    where tablespace_name = 'TEAM_INDEX01');

CONSTRAINT_NAME                INDEX_OWNER
------------------------------ ------------------------------
INDEX_NAME
------------------------------
PK_NOTE_ED_PAYLOAD             MRA
PK_NOTE_ED_PAYLOAD

The following verifies the object above is designated to be on the TEAM_INDEX01 tablespace:

SQL> set long 4000

SQL> select dbms_metadata.get_ddl('INDEX','PK_NOTE_ED_PAYLOAD','MRA') from dual;

DBMS_METADATA.GET_DDL('INDEX','PK_NOTE_ED_PAYLOAD','MRA')

--------------------------------------------------------------------------------

  CREATE UNIQUE INDEX "MRA"."PK_NOTE_ED_PAYLOAD" ON "MRA"."NOTE_ED_PAYLOAD" ("NO

TE_ID")

  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS

  TABLESPACE "TEAM_INDEX01"

The following command corrects this (luckily it's a rather small index):

SQL> alter index  "MRA"."PK_NOTE_ED_PAYLOAD" rebuild tablespace mra_index01 online;

Index altered.

Then the reverification confirms:

SQL> select dbms_metadata.get_ddl('INDEX','PK_NOTE_ED_PAYLOAD','MRA') from dual;

DBMS_METADATA.GET_DDL('INDEX','PK_NOTE_ED_PAYLOAD','MRA')

--------------------------------------------------------------------------------

  CREATE UNIQUE INDEX "MRA"."PK_NOTE_ED_PAYLOAD" ON "MRA"."NOTE_ED_PAYLOAD" ("NO

TE_ID")

  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS

  TABLESPACE "MRA_INDEX01"

So now the DROP command works:

SQL> drop tablespace team_index01 including contents and datafiles;

Tablespace dropped.

I am a bit mystified as to how this might have happened.  I am inclined to believe this object was recreated in this tablespace after having been moved earlier.  I'd be interested in hearing any logical explanations from a reader so inclined to respond.