This discussion is archived
10 Replies Latest reply: Nov 16, 2012 4:43 AM by safazaurs RSS

Cannot drop user schema.

safazaurs Newbie
Currently Being Moderated
Hello.
It's a little bit urgent, I can't drop a user scheme, oracle gives me a set of errors such as:

ORA-00604: error occurred at recursive SQL level 1
ORA-24005: must use DBMS_AQADM.DROP_QUEUE_TABLE to drop queue tables

And I can't find the solution for this issue.

Step by step what I just did:
1) connected to a database with sysdba privs;
2) drop user eks cascade;
here I got errors;

3) and tried to execute thease two select statements:
select object_name,object_type from dba_objects where owner='eks';
select queue_table from dba_queue_tables where owner = 'eks';

and what oracle gave me back is - no rows sellected
And here I stucked, any chance to fix this problem. Solution maybe?
  • 1. Re: Cannot drop user schema.
    Osama_Mustafa Oracle ACE
    Currently Being Moderated
    Post this please :


    select queue_table from dba_queue_tables where owner = 'EKS';
  • 2. Re: Cannot drop user schema.
    Fran Guru
    Currently Being Moderated
    try with upper case:

    select object_name,object_type from dba_objects where owner='EKS';
    select queue_table from dba_queue_tables where owner = 'EKS';
  • 3. Re: Cannot drop user schema.
    safazaurs Newbie
    Currently Being Moderated
    For the first one it executed rows with info, but second one - still nothing.
    I'm getting sad.
  • 4. Re: Cannot drop user schema.
    Fran Guru
    Currently Being Moderated
    why are you using dba_queue_tables?
    http://docs.oracle.com/cd/B19306_01/server.102/b14237/statviews_4039.htm

    like you can read, it's normal that with user EKS doesn't appear anything.

    Edited by: Fran on 16-nov-2012 1:44
  • 5. Re: Cannot drop user schema.
    Osama_Mustafa Oracle ACE
    Currently Being Moderated
    Similar issue but with sysman but can be useful notes for you
    Ora-24005 Error Trying To Drop User Sysman Cascade [ID 456437.1]
  • 6. Re: Cannot drop user schema.
    safazaurs Newbie
    Currently Being Moderated
    Result is the same, there aren't any queue for EKS in all_queue_tables...
    And I'm still wondering, why it gives me this kind of error if there aren't anything what could cause this issue.

    Any solutions, tips?
  • 7. Re: Cannot drop user schema.
    Fran Guru
    Currently Being Moderated
    1)
    BEGIN
    DBMS_AQADM.DROP_QUEUE_TABLE(queue_table => '<QUEUE_TABLE>', force => TRUE);
    END;
    2) drop user xxxx cascade;
  • 8. Re: Cannot drop user schema.
    safazaurs Newbie
    Currently Being Moderated
    I know this way to solve this problem, but there is a problem:

    OWNER QUEUE_TABLE
    ------------------------------ ------------------------------
    EKS_ARH QTABINTERNALNCTS
    EKS_ARH QTABOUTGOINGNCTS
    SYS ALERT_QT
    SYS AQ$_MEM_MC
    SYS AQ_EVENT_TABLE
    SYS AQ_SRVNTFN_TABLE
    SYS KUPC$DATAPUMP_QUETAB
    SYS SCHEDULER$_EVENT_QTAB
    SYS SCHEDULER$_JOBQTAB
    SYS SYS$SERVICE_METRICS_TAB
    SYSMAN MGMT_NOTIFY_QTABLE
    SYSTEM DEF$_AQCALL
    SYSTEM DEF$_AQERROR
    WMSYS WM$EVENT_QUEUE_TABLE

    There isn't EKS which contains any queue tables.

    Edited by: safazaurs on 2012.16.11 04:03
  • 9. Re: Cannot drop user schema.
    Fran Guru
    Currently Being Moderated
    Could you execute:

    select owner, queue_table from dba_queueS where owner='EKS';


    show result
  • 10. Re: Cannot drop user schema.
    safazaurs Newbie
    Currently Being Moderated
    I just tried your select statement and it didn't give me anything (no rows selected).
    But then I tried another one thing what I just wanted to try:
    Like I don't have any needs to save that user I tried to drop all objects one by one using this statement:
    "select 'drop '||object_type||' '|| object_name|| DECODE(OBJECT_TYPE,'TABLE',' CASCADE CONSTRAINTS;',';')
    from user_objects;"

    And then accidently I found out that one of the errors showed me that I have to drop table using this procedure DBMS_AQADM.DROP_QUEUE_TABLE(queue_table => 'QTABWSEORIREQUEST' , force => TRUE);

    Then I found out the similarities and it was this QT, and then I used this select "select object_name from user_objects where object_name like '%QT%'; " to obtain list of thease **** tables. After that droped all thease tables using:
    BEGIN
    DBMS_AQADM.DROP_QUEUE_TABLE(queue_table => 'QTABWSEORIREQUEST' , force => TRUE);
    END;
    /

    helped me...

    So, thank you everyone for helping me.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points