DROP TYPE Statement Hangs

This happened in an Oracle 11 database. I had an INACTIVE session in the database:

SQL> select status from v$session

  2  where sid = 112;

 

STATUS

--------

INACTIVE

 

SQL>

 

I tried to use this session to drop a type but it did not seem to work:

 

SQL> drop type ppc_day_rec

  2  /

… but the status of the session had changed to ACTIVE so I assumed it was doing something:

SQL> l

  1  select status from v$session

  2* where sid = 112

SQL> /

 

STATUS

--------

ACTIVE

 

SQL>

 

Nothing showed up in DBA_WAITERS:

 

SQL> select * from dba_waiters;

 

no rows selected

 

SQL>

 

… but I found what was holding onto the TYPE like this:

 

SQL> l

  1  select a.sid, serial#

  2  from v$access a, v$session b

  3  where a.sid = b.sid

  4* and object = 'PPC_DAY_REC'

SQL> /

 

       SID    SERIAL#

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

       106       1378

 

SQL>

 

I killed the offending session as follows:

 

SQL> alter system disconnect session '106,1378' immediate

  2  /

 

System altered.

 

SQL>

 

… and the DROP TYPE statement completed:

 

SQL> drop type ppc_day_rec

  2  /

 

Type dropped.

 

SQL>