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.
