dbms_aqadm.create_queue_table ORA-00955: name is already used by an existing object

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE    11.2.0.4.0      Production
TNS for 64-bit Windows: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production

I get the following error when running this "create_queue_table" command.  

SQL>
SQL>
SQL> begin
  2        sys.dbms_aqadm.create_queue_table (
  3           queue_table             =>   'QUEUEOWNER.REPORT_REQUEST_QT',
  4           queue_payload_type  =>   'SYS.XMLTYPE',
  5           compatible             =>   '10.0.0',
  6           sort_list             =>   'PRIORITY',
  7           multiple_consumers  =>   TRUE,
  8           message_grouping    =>   DBMS_AQADM.NONE,
  9           secure             =>   FALSE,
 10           storage_clause      =>   'NOCOMPRESS
 11                               TABLESPACE QUEUEOWNER
 12                               RESULT_CACHE (MODE DEFAULT)
 13                               PCTUSED 40
 14                               PCTFREE 10
 15                               INITRANS 1
 16                               MAXTRANS 255
 17                               STORAGE (
 18                                       INITIAL 16K
 19                                       NEXT 16K
 20                                       MAXSIZE UNLIMITED
 21                                       MINEXTENTS 1
 22                                       MAXEXTENTS UNLIMITED
 23                                       PCTINCREASE 0
 24                                       FREELISTS 1
 25                                       FREELIST GROUPS 1
 26                                       BUFFER_POOL DEFAULT
 27                                       FLASH_CACHE DEFAULT
 28                                       CELL_FLASH_CACHE DEFAULT)');
 29  end;
 30  /
begin
*
ERROR at line 1:
ORA-00955: name is already used by an existing object
ORA-06512: at "SYS.DBMS_AQADM", line 81
ORA-06512: at line 2


However, the object does not exist:
SQL> select * from dba_objects where object_name='REPORT_REQUEST_QT';

no rows selected

SQL> select * from sys.obj$ where name='REPORT_REQUEST_QT';

no rows selected
ciphersolAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

slightwv (䄆 Netminder) Commented:
Looks like something in the AQ world got confused at some point in the setup.

There are several steps you can follow to get everything cleaned up on Oracle Support:
Procedures to Remove Orphan AQ Objects Which Fail to Drop via DBMS_AQADM (Doc ID 236898.1)
0
ciphersolAuthor Commented:
I'll take a look at the reference you provided.  

What's weird about my problem is this it is a clean database.  
In fact, after you  made this post, I dropped the entire database and recreated it. I then ran the create scripts and I still get this error.
0
slightwv (䄆 Netminder) Commented:
That is a little weird.

I ran what you posted against my play 11.2.0.3 database.  I removed the schema and tablespace name but it ran just fine.  I could drop it and recreate it at will.

It is starting to sound like it might be a bug.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

ciphersolAuthor Commented:
The problem was with the scripts themselves.  
A sequence (AQ$_REPORT_REQUEST_QT_N), that shouldn't have been created, was being created by the scripts.   The sequence was the cause of the ORA-00955.

Thanks for the input slightwv.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
slightwv (䄆 Netminder) Commented:
Glad you were able to find it.
0
ciphersolAuthor Commented:
The problem was unique to the scripts being executed.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.