Saturday, February 15, 2014

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>

No comments: