Database Software

hi

this is a bit long post, please be patient and go through the whole post.

I am tryin to drop a schema devj2ee with the following command:

SQL> drop user devj2ee cascade;
drop user devj2ee 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

My db is 10gR2 on solaris10 box.

I try to see which queue table exist in the schema:

SQL> select object_name,object_type from dba_objects where owner='DEVJ2EE' and object_name like '%AQ%';

OBJECT_NAME OBJECT_TYPE
---------------------------------------- -------------------
CMN_AQ_MESSAGE_TYPE TYPE
DLS_AQ_QUEUE TABLE

Now if I try to drop this table i get the following error:

SQL> begin
2 DBMS_AQADM.DROP_QUEUE_TABLE('DLS_AQ_QUEUE');
3 end;
4 /
DBMS_AQADM.DROP_QUEUE_TABLE('DLS_AQ_QUEUE');
*
ERROR at line 2:
ORA-06550: line 2, column 1:
PLS-00201: identifier 'DBMS_AQADM' must be declared
ORA-06550: line 2, column 1:
PL/SQL: Statement ignored


SQL> EXECUTE DBMS_AQADM.DROP_QUEUE_TABLE (queue_table => 'DLS_AQ_QUEUE');
BEGIN DBMS_AQADM.DROP_QUEUE_TABLE (queue_table => 'DLS_AQ_QUEUE'); END;

*
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00201: identifier 'DBMS_AQADM' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

So, i thought this is a privelege issue.

SQL> conn sys@dbklsdev as sysdba
Enter password:
Connected.
SQL> begin
2 DBMS_AQADM.DROP_QUEUE_TABLE('DEVJ2EE.DLS_AQ_QUEUE');
3 end;
4 /
begin
*
ERROR at line 1:
ORA-24002: QUEUE_TABLE DEVJ2EE.DLS_AQ_QUEUE does not exist
ORA-06512: at "SYS.DBMS_AQADM_SYS", line 4084
ORA-06512: at "SYS.DBMS_AQADM", line 197
ORA-06512: at line 2

Alternatively, I tried granting privs to devj2ee and tried to drop the table from this user but the result was same.


SQL> GRANT RESOURCE TO devj2ee;
GRANT CONNECT TO devj2ee;
GRANT EXECUTE ANY PROCEDURE TO devj2ee;
GRANT aq_administrator_role TO devj2ee;
GRANT aq_user_role TO devj2ee;
GRANT EXECUTE ON dbms_aqadm TO devj2ee;
GRANT EXECUTE ON dbms_aq TO devj2ee;
GRANT EXECUTE ON dbms_aqin TO devj2ee;

Grant succeeded.

SQL>
Grant succeeded.

SQL>
Grant succeeded.

SQL>
Grant succeeded.

SQL>
Grant succeeded.

SQL>
Grant succeeded.

SQL>
Grant succeeded.

SQL>
Grant succeeded.

SQL> conn devj2ee/devj2ee@dbklsdev
Connected.
SQL> begin
2 DBMS_AQADM.DROP_QUEUE_TABLE('DLS_AQ_QUEUE');
3 end;
4 /
begin
*
ERROR at line 1:
ORA-24002: QUEUE_TABLE DEVJ2EE.DLS_AQ_QUEUE does not exist
ORA-06512: at "SYS.DBMS_AQADM_SYS", line 4084
ORA-06512: at "SYS.DBMS_AQADM", line 197
ORA-06512: at line 2


At this point I tried the same old query again to find out if the queue table really exist:

SQL> select object_name,object_type from user_objects where object_name like '%AQ%';

OBJECT_NAME OBJECT_TYPE
---------------------------------------- -------------------
CMN_AQ_MESSAGE_TYPE TYPE
DLS_AQ_QUEUE TABLE


I hope we have any other way to do this because i really need to drop this schema.