Dropping user hanging

Today I requested that while dropping user request hanged after some time.

Let`s to check:

-bash-3.00$ sqlplus "/as sysdba"

SQL*Plus: Release 11.1.0.7.0 - Production on Wed Oct 23 15:52:36 2013

Copyright (c) 1982, 2008, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> drop user fcjlive cascade;


Checking alert log

--checking alertlog

-bash-3.00$ tail -50 alert_PROD.log
ORA-12012: error on auto execute of job 3149
ORA-01435: user does not exist
Wed Oct 23 16:02:19 2013
Errors in file /u01/app/oracle/diag/rdbms/prod/PROD/trace/PROD_j000_20447444.trc:
ORA-12012: error on auto execute of job 3149
ORA-01435: user does not exist
Wed Oct 23 16:04:19 2013
Errors in file /u01/app/oracle/diag/rdbms/prod/PROD/trace/PROD_j000_20447450.trc:
ORA-12012: error on auto execute of job 3149
ORA-01435: user does not exist
Wed Oct 23 16:06:20 2013
Errors in file /u01/app/oracle/diag/rdbms/prod/PROD/trace/PROD_j000_14614654.trc:
ORA-12012: error on auto execute of job 3149
ORA-01435: user does not exist


Opening another session and checking object counts.

SQL> select count(1) from dba_objects where owner='FCJLIVE';

  COUNT(1)
----------
     40504

SQL> /

  COUNT(1)
----------
     40465

SQL>

As you see count is decrease. But after some period it hanged.

drop user fcjlive cascade

*

ERROR at line 1:

ORA-00604: error occurred at recursive SQL level 1

ORA-24005: must use DBMS_AQADM.DROP_QUEUE_TABLE to drop queue tables

I checking queue tables of mentioned user.

Select OWNER, QUEUE_TABLE from dba_queue_tables where owner = 'FCJLIVE'

no rows.

But there was not any queue table. Suddenly I remember there was error related jobs.

ORA-12012: error on auto execute of job 3149

select * from dba_jobs where job=3149;

Drop job:

exec dbms_job.remove(3149);

commit;

Now execute above statement again.