Dropping QUEUE Table

[RAFF]

Hello All !
I want to drop a QUEUE table from my user TEST schema using

BEGIN
DBMS_AQADM.DROP_QUEUE_TABLE('DEF$_AQCALL');
END;

The error that i m getting back is

ORA-24002: QUEUE_TABLE TEST.DEF$_AQCALL does not exist

But the problem is when i execute the following query .

SELECT * FROM USER_OBJECTS WHERE OBJECT_NAME = 'DEF$_AQCALL'

I found the object by the same name in my user's schema TEST.

Pls let me know how am i going to remove this QUEUE table

Thanks

RAFF

[tamilselvan]

The syntax is wrong.
It should be
EXECUTE DBMS_AQADM.DROP_QUEUE_TABLE (queue_table => 'DEF$_AQCALL');

Tamil

[RAFF]

HI again!
I execute it in a anonymous block .. the sysntax is right ..
Though i used yr syntax .. got the same result. Not working

RAFF

[jmodic]

Originally posted by tamilselvan
The syntax is wrong.
It should be
EXECUTE DBMS_AQADM.DROP_QUEUE_TABLE (queue_table => 'DEF$_AQCALL');

This is exactly the same syntax as Raff is using. The only difference is that you are using named parameter notation while he is using positional notation. But appart from this "cosmetic" difference both shold behave exactly the same.

Raff, are you sure DEF$_AQCALL is indeed a queue table? Does it show up when you isue the following query:

SELECT * FROM DBA_QUEUE_TABLES WHERE OWNER = 'TEST';

You've said the query on DBA_OBJECTS returns an object with object name 'DEF$_AQCALL' under schema TEST. But of what type is this object? I suspect you have a queue (not queue table) defined under schema TEST, but the queue table for this queue is actualy in some other schema. So what you actually want to drop from schema TEST is QUEUE, not QUEUE TABLE. For this you should use DBMS_AQADM.DROP_QUEUE procedure.

[RAFF]

Hi .. again
As i have mentioned earlier DBA_OBJECTS table returns me a record >> containing the followinf information

OWNER : TEST
OBJECT_NAME : DEF$_AQCALL
OBJECT TYPE = TABLE.

i dont find any information about QUEUE in it . But when i issue
DROP TABLE DEF$_AQCALL
return me back with an eeor and to use
DBMS_AQADM.DROP_QUEUE_TABLE procedure to drop the table .. when i us this same procdure .. it returns me back .. that TEST.DEF$_AQCALL does not exist.

I m really stuck in it .. if i m trying to create a queue table in the test schema .. so that i can delete it afterwards .. return me back with the message that
object by the name DEF$_AQCALL already exist in the test scehema. isnt it confusing .. help me out ..


thanks
RAFF

[prince_jr]

Hi, This is just a try . Login to system user and run the dbms scipt.

BEGIN
DBMS_AQADM.DROP_QUEUE_TABLE('TEST.DEF$_AQCALL');
END;

[jmodic]

Raff,

- Oracle Release?

- What do the following queries return:

SELECT queue_table FROM DBA_QUEUE_TABLES WHERE owner = 'TEST';

SELECT owner FROM DBA_QUEUE_TABLES WHERE queue_table = 'DEF$_AQCALL';

SELECT name, queue_table FROM DBA_QUEUES WHERE owner = 'TEST';

SELECT owner, name FROM DBA_QUEUE_TABLES WHERE queue_table = 'DEF$_AQCALL';

SELECT owner, object_type FROM DBA_OBJECTS WHERE object_name = 'DEF$_AQCALL';

[RAFF]

Hello!

The following are the answer of your queries

ORACLE RELEASE : ORACLE 9.2

QUERY1: NO ROWS SELECCTED

QUERY2: SYSTEM

QUERY3: NO ROWS SELECCTED

QUERY4: ORA-00904: "NAME": invalid identifier.

QUERY5:
OWNER OBJECT_TYPE
------------------------------ ------------------
SYS SYNONYM
SYSTEM TABLE
PLS TABLE

Beside i laso want to let you know that the queue table DEF$_AQCALL basiscally exists on SYSTEM schema. But some how due to wrong import of the databse, i m getting it in this TEST schema. Have to remove it.

Thanks

RAFF

[adewri]

Originally posted by RAFF
Hello!

The following are the answer of your queries

ORACLE RELEASE : ORACLE 9.2

QUERY1: NO ROWS SELECCTED

QUERY2: SYSTEM

QUERY3: NO ROWS SELECCTED

QUERY4: ORA-00904: "NAME": invalid identifier.

QUERY5:
OWNER OBJECT_TYPE
------------------------------ ------------------
SYS SYNONYM
SYSTEM TABLE
PLS TABLE

Beside i laso want to let you know that the queue table DEF$_AQCALL basiscally exists on SYSTEM schema. But some how due to wrong import of the databse, i m getting it in this TEST schema. Have to remove it.

Thanks

RAFF

Don't u see from the results that its in SYSTEM schema and not in TEST schema....

[RAFF]

Hi again!
Well ... i know its really getting irritated.
As i told u . that THE QUEUE table DEF$_AQCALL is in SYSTEM schema. If i remove it from there throgh the procedure. I get the +ve result.
But still when i drop the test user

DROP USER TEST cascade;
i get back the following error

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

And if i query the USER_OBJECTS in the test schema.
i get an object of type TABLE of the same name DEF$_AQCALL.

I tried all the steps but couldnt hunt it down.

well. thanks any way

RAFF