Thursday, October 24, 2013

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.


2 comments:

  1. In my case drop user cascade hangs and no alert is generated !!
    How to proceed ??

    Thanks

    ReplyDelete
    Replies
    1. Dear Shameem, first of all sorry for late response. Which version of Oracle you have? Could you provide me last 20 rows of alertlog (when you drop user)? Also check user`s objects, jobs

      Delete