10 Replies Latest reply: Nov 16, 2012 6:43 AM by safazaurs RSS

    Cannot drop user schema.

    safazaurs
      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
          Post this please :


          select queue_table from dba_queue_tables where owner = 'EKS';
          • 2. Re: Cannot drop user schema.
            Fran
            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
              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
                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
                  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
                    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
                      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
                        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
                          Could you execute:

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


                          show result
                          • 10. Re: Cannot drop user schema.
                            safazaurs
                            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.