Dropping QUEUE Table
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 10 of 10

Thread: Dropping QUEUE Table

  1. #1
    Join Date
    May 2003
    Posts
    32

    Dropping QUEUE Table

    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

  2. #2
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    The syntax is wrong.
    It should be
    EXECUTE DBMS_AQADM.DROP_QUEUE_TABLE (queue_table => 'DEF$_AQCALL');

    Tamil

  3. #3
    Join Date
    May 2003
    Posts
    32
    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

  4. #4
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    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.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  5. #5
    Join Date
    May 2003
    Posts
    32
    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

  6. #6
    Join Date
    Oct 2003
    Posts
    38
    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;

  7. #7
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    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';
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  8. #8
    Join Date
    May 2003
    Posts
    32
    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

  9. #9
    Join Date
    Nov 2002
    Location
    New Delhi, INDIA
    Posts
    1,796
    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....
    Amar
    "There is a difference between knowing the path and walking the path."

    Amar's Blog  Get Firefox!

  10. #10
    Join Date
    May 2003
    Posts
    32
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width