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>
